Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hey!
I have a date filter that lets me select a date, and I want to find the cumulative from that date + last 11 months
Using the table below, Id like to pick Jan 2021 and see 11 as the cumlative total (Feb 2020 + Mar 2020+ Jun 2020 + Jan 2021)
Whilst if I pick Feb 2021 I would see 8 ( Mar 2020 + Jun 2020, Jan 2021 + Feb 2021)
Date | Instance |
Jan 2020 | 1 |
Feb 2020 | 4 |
Mar 2020 | 2 |
Jun 2020 | 3 |
Jan 2021 | 2 |
Feb 2021 | 1 |
Is there a Dax formula that could easily solve this?
Thanks!
*Rolling Cummulative Total for last 3 Year based on Selected Year*
I have scenario to show “cumulative total in each year” so that if any month is selected then it will show sum till that month only in that year.
we can get "cumulative total in each year" by DatesYTD and it want based on month filter also
But then I want to show this result for last 3 year based on current year selected in year filter.
I have written measure like
Cumulative Rolling KPI Score =
Var ScoreYTD =
CALCULATE( SUM( 'KPI Fact'[KPI Actual] ), DATESYTD( DimDate[Date] ))
Var Result =
CALCULATE( ScoreYTD ,
DATESINPERIOD(DimDate[Date], MAX( DimDate[Date]) , -3, YEAR )
Return
Result
But this show result only for 1 year instead of for last three year.
Expected result
Based on current year select
Getting result as
@Pandetbaby , You can get rolling 12 like the example below with help from date table
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
22 | |
12 | |
8 |
User | Count |
---|---|
77 | |
50 | |
46 | |
16 | |
12 |