Getting the selected period from filters

[Mengelhardt]

Hi all,

I have a special question again.

I have a lot of sales data from various periods (say months). For simplification they are numbered  1,2, 3,..... 

1. Task: I have to make a KPI with the sales of actual month as the first KPI and the difference to the last month as the second KPI. My unsatisfying solution so far ist to hardcode the last month in a variable. But with every new data I have to adjust that. It would be great if I could just take the max(month) for calculation but that does not work for the difference as max(month)-1 is not recognized.

2. Task: Take an abitrary month as a Filter and calculate the sales of this selected month (first KPI) and the difference to the preceding month (second KPI).  

Any help is highly appreciated!

Best Regards

Marc

[HugoRomeira_PT]

Hi Marc,

If the granularity of your data model allows you to see Sales per Day, it would mean that you have a Date field in the data model.

If yes, you could use this in set analysis and so achieve your requirement using the following expressions:

Actual Month

sum({<Date={">=$(=MonthStart(Today())) <=$(=MonthEnd(Today()))"}>}[Sales])

Previous Month

sum({<Date={">=$(=MonthStart(AddMonths(Today(),-1))) <=$(=MonthEnd(AddMonths(Today(),-1)))"}>}[Sales])

Hope it helps.

[Mengelhardt]

Thanks a lot Hugo,

In fact I just have the months, numerated by 1,2,..12,13,14, ...,,n. So I really need to take numbers instead of date functions.

Best Regards

Marc

[HugoRomeira_PT · Accepted solution]

Hello,

Ok, I understand...

So, assuming that the current month is equal to the max(month) available, and that the month field is an incremental integer ID 🙂

Meaning:

June 2022 = 14

May 2022 = 13

April 2022 = 12

March 2022 = 11

...

Actual Month:

sum({<[month]={$(=Max([month]))}>}[Sales])

Previous Month:

sum({<[month]={$(=Max([month])-1)}>}[Sales])

Hope it helps!

[Mengelhardt]

Thanks Hugo! 

Great, That's it!

Have a nice day!!

Best and happy Regards

Marc