topic Re: how to create current month vs previous month line chart in DAX Commands and Tips

how to create current month vs previous month line chart

eliasayyy — Thu, 06 Jul 2023 09:19:05 GMT

hello everyone i have a slicer that lets me seelct a month ,

when i select for example june , i can see the dates of junes on the x-axis and the value of june

what i want is how can i add last month so may on the chart while keeping structure of date to june?

so i want a dotted line for previous month that can show how we performed this month vs last one

Re: how to create current month vs previous month line chart

johnt75 — Thu, 06 Jul 2023 09:54:40 GMT

If your current measure is [Value] then you can create a new measure like

Value last month = CALCULATE ( [Value], DATEADD ( 'Date'[Date], -1, MONTH ) )

Re: how to create current month vs previous month line chart

WinterMist — Thu, 06 Jul 2023 10:30:59 GMT

johnt75

Trying to learn from you regarding the q from eliasayyy

I can see that your measure works for my test data.

However, I wrote a similar measure which displays no line on the line chart.

Are you able to help me understand why it displays the exact same value as your measure within the card visual, but displays nothing at all on the line chart?

Regards,

Nathan

Re: how to create current month vs previous month line chart

johnt75 — Thu, 06 Jul 2023 10:41:59 GMT

When a table is marked as a date table it has some special behaviour. If you apply a filter onto the Date column then filters on any other columns of the date table are automatically removed. That means that in my code all other filters on the date table are removed, but in your version there are 2 conflicting filters in place. The first filter is on the date, from the chart, and the second which you place on the year month column. e.g. it is filtering rows where the date is June 18 and the year month is May 2023 - that returns no rows so the measure is blank.

Re: how to create current month vs previous month line chart

WinterMist — Thu, 06 Jul 2023 11:11:09 GMT

johnt75

Thanks very much for teaching me this special behavior. (Can't believe I've been working with DAX for 2 years without knowing this.)

1) Does it only apply to certain visuals then?

Because it doesn't impact the card the same way as the line chart.

The card also has the same 2 conflicting filters in place, yet the measure is not blank within the card.

It shows the correct Sales for the previous month, just like your measure.

2) I have often seen other measures on Power BI forums filter other fields in the 'Date' (or 'Calendar') table aside from the 'Date'[Date] column without running into this same issue. Would you guess that all those measures simply work due to the lucky absence of additional conflicting date filters? (Now I want to go back & check. LOL.)

Thanks again!

Nathan

Re: how to create current month vs previous month line chart

johnt75 — Thu, 06 Jul 2023 11:18:06 GMT

1). The card visual doesn't have the same filters - it only has a filter on year month, the chart has a filter on date as well. Your measure replaces the filter on year month, so the card visual works.

2). I would think that the other measures you have seen work because they are replacing existing filters on the same columns. Otherwise they would need to use REMOVEFILTERS

Re: how to create current month vs previous month line chart

WinterMist — Thu, 06 Jul 2023 11:27:25 GMT

Ah, ok. That makes sense. Thanks again very much for explaining.