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, fellas.
I tell you that in the next report I want to show on the charts, 12 months, from the filtered month on the sheet, backwards.
You could make a slicer so that you can filter the charts, but the customer wants the chart to automatically show 12 months backwards when selecting the month above.
I found other solutions in this forum, but it should be noted that the filter above, also affects the calculations in the table below.
As a fact, I already have a date table.
Please stay tuned for some guidance.
Best regards
Solved! Go to Solution.
Due to I don't know your data model, I build a sample to have a test.
Table(Add Year column and Month column by calculated column):
Year = YEAR('Table'[Date])
Month = MONTH('Table'[Date])
Date Table:
Date = ADDCOLUMNS(CALENDARAUTO(),"Year",YEAR([Date]),"Month",MONTH([Date]))
I build two measures, one show piror 12 month values and one show future 12 month values.
Piror 12 Month =
Var _Year = SELECTEDVALUE('Date'[Year])
Var _Month = SELECTEDVALUE('Date'[Month])
Var _MaxDate = MAXX(FILTER(ALL('Date'),'Date'[Year]=_Year&&'Date'[Month]=_Month),'Date'[Date])
Var _Piror12Month = EOMONTH(_MaxDate,-12)+1
Return
CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Date]<=_MaxDate&&'Table'[Date]>=_Piror12Month))
Future 12 Month =
Var _Year = SELECTEDVALUE('Date'[Year])
Var _Month = SELECTEDVALUE('Date'[Month])
Var _MinDate = MINX(FILTER(ALL('Date'),'Date'[Year]=_Year&&'Date'[Month]=_Month),'Date'[Date])
Var _Future12Month = EOMONTH(_MinDate,11)
Return
CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Date]<=_Future12Month&&'Table'[Date]>=_MinDate))
Result is as below.
Default will show blank.
Select Year = 2019, Month = 2 in Slicers.
You can download the pbix file from this link: Display on a chart 12 months ago, starting with the selected month. Help
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Due to I don't know your data model, I build a sample to have a test.
Table(Add Year column and Month column by calculated column):
Year = YEAR('Table'[Date])
Month = MONTH('Table'[Date])
Date Table:
Date = ADDCOLUMNS(CALENDARAUTO(),"Year",YEAR([Date]),"Month",MONTH([Date]))
I build two measures, one show piror 12 month values and one show future 12 month values.
Piror 12 Month =
Var _Year = SELECTEDVALUE('Date'[Year])
Var _Month = SELECTEDVALUE('Date'[Month])
Var _MaxDate = MAXX(FILTER(ALL('Date'),'Date'[Year]=_Year&&'Date'[Month]=_Month),'Date'[Date])
Var _Piror12Month = EOMONTH(_MaxDate,-12)+1
Return
CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Date]<=_MaxDate&&'Table'[Date]>=_Piror12Month))
Future 12 Month =
Var _Year = SELECTEDVALUE('Date'[Year])
Var _Month = SELECTEDVALUE('Date'[Month])
Var _MinDate = MINX(FILTER(ALL('Date'),'Date'[Year]=_Year&&'Date'[Month]=_Month),'Date'[Date])
Var _Future12Month = EOMONTH(_MinDate,11)
Return
CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Date]<=_Future12Month&&'Table'[Date]>=_MinDate))
Result is as below.
Default will show blank.
Select Year = 2019, Month = 2 in Slicers.
You can download the pbix file from this link: Display on a chart 12 months ago, starting with the selected month. Help
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
You may refer to my solution here - Flex a Pivot Table to show data for x months ended a certain user defined month.
Hope this helps.
@AbelardoRubilar , Beast is that you use relative date slicer.
Or you can use rolling measure
example
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-12,MONTH))
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX(Sales[Sales Date]),-12,MONTH))
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))
But they can rollup the data into one date. So need another independent date table to achieve -
https://www.youtube.com/watch?v=duMSovyosXE
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 |
---|---|
107 | |
105 | |
88 | |
74 | |
66 |
User | Count |
---|---|
126 | |
111 | |
100 | |
83 | |
71 |