How to display last n months in Powerpivot?

0
votes

I better apologize right away, I am new to StackOverFlow, so please forgive me, if I left out any information. This website and its community seems to be really helpful and since Ive been spending almost two days on the following problem, I figured you guys might be my last chance!

I am working with (and still learning about) Microsoft Excel PowerPivot. In Powerpivot I have a usual Date Table, which has a column for each date within the years 2017 till 2021 and several columns with year, month, quarter, etc. The Date Table is connected with my Fact Table and so far everything has been working out just fine.

Now I'd like to create a Pivottable that displays only the last 12 months (including the current month). So right now I'd like the Pivottable to show me all the data of 2020-1, 2019-12, 2019-11, 2019-10, 2019-9, 2019-8, 2019-7, 2019-6, 2019-5, 2019-4, 2019-3 and 2019-2. And I want the data to update itself, so once February has started the Pivottable is supposed to show the months 2020-2, 2020-1, 2019-12,...,2019-3.

I believe all I have to do is go to my Date Table and create (at least) a calculated column saying for example "YES" for all the dates within the last 12 months and "NO" for all the other dates. And this is where I need your help, because no matter what Dax codes I try, I cant get it done.

So far Ive only been able to create a calculated column called "Last365Days" with the following code:

=IF(DimDate[DimDateKey]>TODAY()-365;"YES";"NO")

DimDate is the Date table and DimDateKey is the column with alle the dates within the years 2017 till 2020. However in this case the dates being flagged with a "Yes" start at exactly one year ago, which is 25 January 2019, but I need them to start at 1 February 2019 and end at the end of the current month.

Im sorry for this big text, I hope Ill be able to describe future problems in less words. Anyway if anyone of you has a solution for this, Id really really appreciate it.

Thank you very much in advance and enjoy your weekend!

Dave

1
exceldaxbusiness-intelligencepowerpivot
Take a look at the DAX DATEDIFF function – Ron Rosenfeld

1 Answers

0
votes

Thank you for your advise, Demogorii. Unfortunately I couldn't figure out how the DATEDIFF function could be useful for my problem, since it only returns the amount of time between two dates, right?

However I did manage to find a solution:

=IF(DimDate[DimDateKey]>EOMONTH(TODAY();-12)&&DimDate[DimDateKey]<=TODAY();FORMAT(DimDate[DimDateKey];"YYYY-MM");"EARLIER")

With this calculated column I can display the last twelve months and it should update itself every day. In this case the displayed data starts with the first of the month one year ago and ends today, which is just fine since there are is of course no future data. I guess I'll have to wait till February to make sure everything updates itself correctly.