topic How to compare current selected month and the month before's data in DAX Commands and Tips

How to compare current selected month and the month before's data

Anonymous — Wed, 25 Jan 2023 10:19:32 GMT

Hi all,

I have a dataset which records the value of three KPIs each month.

I currently have a multirow card which displays these KPIs, connected to a slicer so that I can see each month's totals. For example, for January 2023:

I would like to display next to this card the difference between the selected month's values and month prior to it. For example, if January 2023 were selected, I'd like it to show +5 for number of activity cards (because 0 were produced in Dec 2022), +2 articles and +3 Lunch and Learns.

How is the best way to do this? I am a complete Power BI novice, so I'm not sure where to start!

Thanks,

Re: How to compare current selected month and the month before's data

FreemanZ — Wed, 25 Jan 2023 11:29:17 GMT

hi Anonymous

you would need several measures like:

number of article PM =

CALCULATE(

SUM(TableName[KPI Value]),

TableName[KPI name] = "Number of articles",

TableName([Date]) = EDATE(MAX(TableName([Date])), -1)

)

number of activity card PM =

CALCULATE(

SUM(TableName[KPI Value]),

TableName[KPI name] = "Number of activity card",

TableName([Date]) = EDATE(MAX(TableName([Date])), -1)

)

number of launch and learns PM =

CALCULATE(

SUM(TableName[KPI Value]),

TableName[KPI name] = "Number of Launch and Learns",

TableName([Date]) = EDATE(MAX(TableName([Date])), -1)

)

number of article MoM =

[number of article] - [number of article PM]

number of activity card MoM =

[number of activity card] - [number of activity card PM]

number of launch and learns MoM =

[number of launch and learns] - [number of launch and learns PM]

Re: How to compare current selected month and the month before's data

Anonymous — Wed, 25 Jan 2023 12:13:05 GMT

I'm sorry FreemanZ, I don't really understand. If we zoom in on the KPI for "Number of articles", could you explain what the expected output of each of those measures would be?

I tried doing the first measure, but it just returns (blank) when put it into a card unless I have multiple dates selected. Even then, I don't really understand what the number returned refers to. For example, if I selected Februay and March 2023, "1" is returned.

Ideally, if I had just March highlighted, I'd want to return February's number (so I can then calculate the difference between the two).

Thanks so much,

Re: How to compare current selected month and the month before's data

FreemanZ — Wed, 01 Feb 2023 01:59:27 GMT

hi Anonymous

could you paste your dataset again as editable text and lemme verify the code?