98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsForum home » Delegate support and help forum » Microsoft Power BI training and help » Dynamic Date Filter in measure
Dynamic Date Filter in measure
Resolved · Low Priority · Version 2016
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
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Power BI tip:Query Editor ShortcutsCtrl + E: Open or close the Query Editor. |