topic Changes / Variance over time in DAX Commands and Tips

Changes / Variance over time

joshua1990 — Sat, 10 Oct 2020 15:35:04 GMT

Hey community!

I have a sales table with the following columns:

Order Nr
Order Date
Value
Item

I also have a budget table:

Item
Budget
Month

These tables are linked with the Item Key and also with the calendar table.

Then I have a simple measure like

DIVIDE ( [Sales], [Budget])

Now I would like to show the change per day, per week, and per month. I would like to use a drill down from month to week and to date.

For Instance

01.01.2020 = 95%

02.01.2020 = 98%

The change would be = 3%.

How would you call such a measure?

And how would you build that?

Re: Changes / Variance over time

daxer-almighty — Sun, 11 Oct 2020 16:18:31 GMT

If you want to use a standard calendar to do this, it'll be almost impossible. For weeks you have to use one of the industry-standard calendars, for instance, the ISO-week calendar. This is because weeks do not go evenly into months....

Re: Changes / Variance over time

joshua1990 — Sun, 11 Oct 2020 18:53:20 GMT

daxer-almighty @Thanks! Actually I am working with a fiscal calendar with specific start dates for each fiscal month based on the 445 approach.

that should work, right?