Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Do you know how to create a dynamic measure for trailing 3 months, based upon a slicer date range? For example, in November, it would calculate Aug, Sept, Oct..... and in Dec, it would calculate Sept, Oct, Nov.
Solved! Go to Solution.
Sure, it's going to be a variation on my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...
Essentially, create a VAR that is MAX([Month]) - 1. This is your selected month and it should be numeric. Then you create another VAR that is that variable minus 2. Then it is a simple matter of filtering ALL of your table where the numeric month is between those two values.
Hi @smignogna
Here is a test pbix you may refer to
create a calendar date table, don't connect this date table to your table, this calendar date table would automatically generate the date according to your tables.
Table = CALENDARAUTO()
create "year" and "month" column inside this calendar date table
year = YEAR([Date]) month = MONTH([Date])
Then add [year] and [month] column in the slicers
Next create measures in your table
Measure = CALCULATE ( SUM ( Sheet1[values] ), FILTER ( Sheet1, YEAR ( [date] ) = SELECTEDVALUE ( 'Table'[year] ) && MONTH ( [date] ) < SELECTEDVALUE ( 'Table'[month] ) && MONTH ( [date] ) >= SELECTEDVALUE ( 'Table'[month] ) - 3 ) )
Best Regards
Maggie
Sure, it's going to be a variation on my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...
Essentially, create a VAR that is MAX([Month]) - 1. This is your selected month and it should be numeric. Then you create another VAR that is that variable minus 2. Then it is a simple matter of filtering ALL of your table where the numeric month is between those two values.
Thank you so much Greg! This worked perfectly!