dynamic date filter measure

Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Power BI training and help » Dynamic Date Filter in measure

Dynamic Date Filter in measure

resolvedResolved · Low Priority · Version 2016

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

 

Training courses

 

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
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

Power BI tip:

Query Editor Shortcuts

Ctrl + E: Open or close the Query Editor.
Ctrl + F: Find within the Query Editor.
Ctrl + H: Replace within the Query Editor.
Ctrl + D: Duplicate selected query.
Ctrl + ; (semicolon): Insert a step to create a custom column.
Ctrl + M: Enter the formula bar for the selected step.

View all Power BI hints and tips


Server loaded in 0.07 secs.