topic Measures for Future Quarters in DAX Commands and Tips

Measures for Future Quarters

KatieH — Wed, 05 Apr 2023 14:28:33 GMT

Hi all,

I have a dataset which has a list of orders, their values and their estimated arrival date.

I need to create 4 separate sum measures which show the sum of the values for the current and next 3 quarters. As the quarters will change as we move through the year, I don't want to hardcode the dates. Can anyone help me with the DAX for creating these?

Re: Measures for Future Quarters

Uspace87 — Wed, 05 Apr 2023 15:16:52 GMT

KatieH

What I usually do for "Orderbook" is this:

- I have a calculated column in my "Date_table" which classify the dates based on the month (you can apply the same logic on the Quarter):

Table.AddColumn(Source, "Order_Due_Period", each if [MonthsFromActualMonth] = 0 then "Current"

else if [MonthsFromActualMonth] = -1 then "Previous "

else if [MonthsFromActualMonth] > 0 then "Future"

else "BackLog")

-The of course you connect the date on your date table with your "Estimate Arrival date" and using always the same Measure you can calculate the value of all the orders that are supposed to arrive in the current quarter, previous, future etc...

Re: Measures for Future Quarters

KatieH — Wed, 05 Apr 2023 15:24:51 GMT

Thanks for the reply Uspace87 - the bit I'm struggling with is grouping them into actual quarters actual quarters going forward. So as it stands right now, assuming that the quarter we're in is Qtr 1, an order for the 3rd Sept would be summed into the Qtr 2 bucket but as soon as we tip into the 1st July, I'd want it to sum into the Qtr 1 bucket. Does that make sense?