Create a Rolling Month Filter given a date filter

Question

I have a graph that plots revenue. I need a filter that filters the dates on the graph to show only the current month depending on the selected date. The graph should be a rolling month.

If the selected date is March 10, the graph should show March 1-10.

If the selected date is Febraury 28, the graph should show February 1-28.

If the selected date is February 5, the graph should show February 1-5.

This is my calculation for the last sale date given the selected date:

TS_LASTSALESDATE2 = CALCULATETABLE ( LASTNONBLANK ( DimDate[DateKey], 0), ALLSELECTED(FactSales))

This is my attempt at a filter that didn't work:

Filtered Month = IF(FORMAT(LASTNONBLANK(DimDate[DateKey],0),"YYYYMM")=FORMAT([TS_LASTSALESDATE2],"YYYYMM"),"YES","NO")

Appreciate the help!

amitchandak · Answer

cbolling , with help from a&nbsp; date table use datesmtd
&nbsp;
example
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
&nbsp;
Power BI — Month on Month with or Without Time Intelligence https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090ehttps://www.youtube.com/watch?v=6LUBbvcxtKA

cbolling · Answer

Hi&nbsp;amitchandak&nbsp;,&nbsp;I do not want to display MTD sales, I want to display sales of each day in the month, but the X axis is the day of the month, and next week on the first day it would jump back to 1.

Forum Discussion

Create a Rolling Month Filter given a date filter

2 Replies

Related content

Rolling accumulation

Rolling accumulation

Matrix Row Filtering Alters Rolling DATESINPERIOD Measures

Creating a Date Filter in Power BI

Rolling Date Identifiers