Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
brunofds
Helper I
Helper I

Unusual QUARTERS - DAX

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.

 

 

Screenshot 2022-11-04 104321.png

 

 

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:

Screenshot 2022-11-04 102831.png

 

 

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

 

 

11 REPLIES 11
aj1973
Community Champion
Community Champion

Hi @brunofds 

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

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

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
Community Champion
Community Champion

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

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

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
Community Champion
Community Champion

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

Nice exemple @mangaus1111 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

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.

 

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:

 

brunofds_0-1667575727095.png

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

brunofds_1-1667575856682.png

 

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

Screenshot 2022-11-04 124600.png

 

 

 

thanks

aj1973
Community Champion
Community Champion

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.

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

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‬

 

 

brunofds_0-1667582566159.png

 

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
Community Champion
Community Champion

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

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

Hey

 

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.