topic Re: Create a Rolling Month Filter given a date filter in DAX Commands and Tips

Create a Rolling Month Filter given a date filter

cbolling — Thu, 11 Mar 2021 20:03:38 GMT

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!

Re: Create a Rolling Month Filter given a date filter

amitchandak — Fri, 12 Mar 2021 05:48:26 GMT

cbolling , with help from a date table use datesmtd

example

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))

Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA

Re: Create a Rolling Month Filter given a date filter

cbolling — Wed, 24 Mar 2021 21:12:17 GMT

Hi amitchandak ,

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.