Akash has attended:
Power BI Modelling, Visualisation and Publishing course
Dynamic Date Filter in measure
Hi,
So I am working with a data set which has a the following dimensions: Table is called = 'Marvin Units'
Title,Transaction Year.Month,'Data (Units Sold)' and Release Year.Month)
I would like to create a dynamic measure which calculates the first 12 months 'Total Units' since it's release month and changes depending on the Title.
I have tried the following formula (2nd function) but have only been getting errors when putting the measure into a visual:
'Total Units' = CALCULATE(SUM('Marvin Units'[Data 'Units Sold']))
'First 12 months Total Units' = CALCULATE([Total Units],'Marvin Units'[Release Year.Month] <= CONCATENATE(LEFT(ALLSELECTED('Marvin Units'[Release Year.Month],4)+1,RIGHT(ALLSELECTED('Marvin Units'[Release Year.Month],2)))
Their are no errors when writing this formula however there is an error showing when placing into a visual.
An example of the desired result is:
Title '123' Release Year.Month = 2018.12
Title '123' = 1200 Total Units Sold
Title '123' = 700 Total Units Sold from 2018.12 to 2019.12
I hope my example and thought process is clear to understand.
Thanks in advance,
Akash
RE: Dynamic Date Filter in measure
Hi Akash,
Thank you for the forum question.
How do you have the dates? in the columns:
Transaction Year.Month and Release Year.Month
2020.02 or 2020.February
It is very important to have a date table in the model. You will have to change the dates in the two columns to a date format Power Bi can recognise. This can be done in the query editor.
Then you need to connect to a date table.
When you have done this you have many date intelligent functions, which can do what you want.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector