Unusual QUARTERS - DAX

[brunofds]

Hello there. Dealing with a problem here in my dataset.  

For the sake of simplicity, I have a simple database with DimDate (from jan.1 to December 31) and FactOrders, using NorthWindDataset for this example (but my case is more complex)

What I need: for these measures, my QUARTERS will be unusual. 

Q1: December (previous year), Jan. and Feb.

Q2: Mar. Apr. and  May

Q3: ...

Q4: Sep. , Oct. and Nov.

 

Current measures:

Sales = SUMX(fOrder_Details,fOrder_Details[Quantity] * fOrder_Details[UnitPrice])

SalesPQ = CALCULATE( [Sales], PREVIOUSQUARTER('Date'[Date]))  (not used, just for comparison)

So how I handle this?  My view will be something like this:

So, in the example above, if a user select 1997 and Q1, the value should display the sum of Dec. 96 + Jan.97 + Feb. 97

 

Thanks

[aj1973]

Hi @brunofds 

You need to change the structure of your DimDate, meaning that the Calendar Year should start from December 1st

[brunofds]

Hey there. Thanks for your reply.

Unfortunately i can't because i need to show my other measures in the current method, from Jan. 1 to Dec.31. 

This is an exception...

[aj1973]

You can add another Date Table or add other columns to your DimDate and use Inactive Relationship for calculations.

[brunofds]

Adding other columns to my existing table would be the best choice right now.

Could you be more specific?

I've already tried to add a new "Quarter Column", but with no luck regarding to year filters...

[aj1973]

Basically you start from adding an Index column like @mangaus1111 did and the rest will follow.

Nice exemple @mangaus1111 

[mangaus1111]

HI @brunofds ,

see my pbi file.

https://1drv.ms/u/s!Aj45jbu0mDVJizydWjv7d9vRhovx?e=vfevOe

You have to add 4 additional columns named "Financial Quarter", "Financial Quarter Number" ,"Financial Year" and "Index"

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[brunofds]

Hey thanks for your time.

As i said to another user, I need a conventional DimDate Table.

So in your example, my slicers wont be Financial Years, but the years itself:

also using your example, the amount of 2020 would be the regular SUM:

I need to create another measure but in this case, consider my "unusual quarter".

thanks

[aj1973]

Hey @brunofds 

You now need to work on your calculated measures accordingly, probably add an Inactive relationship and use it to get the right display.

[brunofds]

I'm not complete aware on how to solve this...

Using the example above mentioned (https://easyupload.io/ianzro) , how can I turn the "average quarter" into the "correct" result, which is: 

50+ 100 + 40

/ 3  

 = 63,33333333333333‬

or the sum of amount (Amount_ measure_)  into 190?

I'm aware of the filters and whats wrong here, but not sure how to solve it.

Thanks

[aj1973]

There could be different ways to solve it but the best way left is to share a sample of your pbix.

[brunofds]

Hey

I did leave a pbix example in my previous message: https://easyupload.io/ianzro