topic Re: Rolling revenu up till selected month shown in Graph in DAX Commands and Tips

Rolling revenu up till selected month shown in Graph

DelVosa — Mon, 07 Feb 2022 14:33:50 GMT

Hi All

I am struggling to display the rolling monthly revenue by clicking a filter linked to the months.

Based on the period filter (right top) I focus on the results of that specific month and the year to date results. The monthly and ytd revenue is calculated as follows and works fine (red circle):

Rev = sum('AS$Projects'[Opbrengsten])

Rev ytd = TOTALYTD([Rev],DatumTabel[DatumParameter],all(DatumTabel[DatumParameter]),"31/08")

I now want to show the rolling revenue mtd and ytd in a graph (yellow circle) based on the selection of the month.

It works when I disable the interaction between the month filter and the graph and I manually select the months I want to show. But this is static! When the user swithes the filter to NOV, the graph doesn't change off course. I want this to be dynamic.

I would like to have the visualization of the rolling revenue linked to the filter. If I click Dec, i would like the graph to show Sep-Dec. If I click Nov, I would like to see Sep-Nov

Is there a way to do this?

By clicking one filter i would like to see the MTD results, YTD results, rolling MTD up till that month and rolling YTD of the month.

One important remark: my year starts on first of September and not 01/01

Thanks a lot!

Regards

Jordy

Re: Rolling revenu up till selected month shown in Graph

amitchandak — Mon, 07 Feb 2022 15:11:09 GMT

DelVosa , if you want measure switch, You can use measure slicer

measure slicer
https://www.youtube.com/watch?v=b9352Vxuj-M
https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/500115
https://radacad.com/change-the-column-or-measure-value-in-a-power-bi-visual-by-selection-of-the-slicer-parameter-table-pattern
https://www.youtube.com/watch?v=vlnx7QUVYME

But if you want to changes axis column, then you have use bookmarks

Axis Slicer
Dynamically change chart axis in Power BI
bookmark -https://blog.crossjoin.co.uk/2018/04/20/dynamically-changing-a-chart-axis-in-power-bi-using-bookmarks-and-buttons/
https://radacad.com/bookmarks-and-buttons-making-power-bi-charts-even-more-interactive

Re: Rolling revenu up till selected month shown in Graph

V-lianl-msft — Fri, 18 Feb 2022 08:52:51 GMT

Hi DelVosa ,

Please try to create a measure like below and use the month from fact table as x-axis.

Measure = var _max = MAX('Date'[Date]) return CALCULATE(SUM(financials[ Sales]),DATESYTD(('Date'[Date]),"8/31"),'Date'[Date]<=_max)

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.