topic Re: DAX Compare N number of days across Months in DAX Commands and Tips

DAX Compare N number of days across Months

noircrk — Thu, 23 Mar 2023 20:51:41 GMT

I have a date slicer that generally is used for MTD. For example my slicer shows March 1st - March 20th and a have a single bar on a bar graph that shows the number of widgets sold for that time period. What I need to add is the number of widgets sold Feb. 1st - Feb 20th as a bar, Jan1st- Jan20th as a bar and so on for the 12 previous months.

I would need this to change based on the selection of the slicer, so if I change it to Mar 1st - Mar 25th the other data points would adjust for 1st-25th in their respective months.

How would I go about accomplishing this??? Any and all suggestions are welcome.

Re: DAX Compare N number of days across Months

wdx223_Daniel — Fri, 24 Mar 2023 03:21:48 GMT

MTD = VAR _year=2008 VAR _month=3 VAR _day=31 RETURN CALCULATE(TOTALMTD([Sales Amount],'Date'[Date]),FILTER('Date','Date'[Month Number]<=_month&&DAY('Date'[Date])<=_day&&'Date'[Calendar Year Number]=_year))

Re: DAX Compare N number of days across Months

noircrk — Fri, 24 Mar 2023 14:23:53 GMT

Thank you wdx223_Daniel ! Could you help me understand what it is this formula does? Variables for Year, Month and Day I wouldn't think would be hardcoded with values and I don't see any mentions of getting the prior months data.

When I adapt this to my data the result is empty.

Re: DAX Compare N number of days across Months

noircrk — Fri, 24 Mar 2023 17:36:38 GMT

I have solved my problem using the below video by parry2k :

https://www.youtube.com/watch?v=UG1WxkBkM48

This solution worked perfectly for my need.

Re: DAX Compare N number of days across Months

parry2k — Fri, 24 Mar 2023 22:19:18 GMT

noircrk glad to hear that my youtube video helped to solve your problem. Thanks!