Percentage of total on QoQ basis in power bi

[Anonymous]

I need to calculate percentage of total on QoQ basis in power bi

I have created measure 1 which is 

% OF Total_Current month_CF =

var denominator =

CALCULATE(SUM('fred_h8_banking_03132023'[Amt in Bn_CF]),ALLEXCEPT('fred_h8_banking_03132023',fred_h8_banking_03132023[A/L],'fred_h8_banking_03132023'[Month],'fred_h8_banking_03132023'[banks]))

var numerator =

CALCULATE(SUM('fred_h8_banking_03132023'[Amt in Bn_CF]),ALLEXCEPT('fred_h8_banking_03132023',fred_h8_banking_03132023[A/L],'fred_h8_banking_03132023'[Month],fred_h8_banking_03132023[series_title],'fred_h8_banking_03132023'[banks]))

return DIVIDE(numerator, denominator,0)

This will give me result for say Jan 2020.

2nd measure 

% OF Total_Previous Quarter_CF =

var denominator =

CALCULATE(SUM('fred_h8_banking_03132023'[Amt in Bn_CF]),DATEADD(fred_h8_banking_03132023[Month],-1,QUARTER),ALLEXCEPT('fred_h8_banking_03132023',fred_h8_banking_03132023[A/L],'fred_h8_banking_03132023'[Month],'fred_h8_banking_03132023'[banks]))

var numerator =

CALCULATE(SUM('fred_h8_banking_03132023'[Amt in Bn_CF]),DATEADD(fred_h8_banking_03132023[Month],-1,QUARTER),ALLEXCEPT('fred_h8_banking_03132023',fred_h8_banking_03132023[A/L],'fred_h8_banking_03132023'[Month],fred_h8_banking_03132023[series_title],'fred_h8_banking_03132023'[banks]))

This will give me result for say Oct 2019.

These measures are finally used in this way to calculate QoQ

MoM % change_CF =

VAR a = [% OF Total_Current month_CF]

VAR b = [% OF Total_Previous Quarter_CF]

return  DIVIDE( a-b,a,0)

The answer I am getting is not correct. Please guide.

[amitchandak]

@Anonymous , for time intelligence always use date table, joined with your date table

and you can avoid allexpect

CALCULATE(SUM('fred_h8_banking_03132023'[Amt in Bn_CF]),DATEADD(Date[Date],-1,QUARTER))

The date of date table  is joined with date of your table

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5bd4
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[Anonymous]

@amitchandak 

Hi Amit, thank you for responding. I'll create a table for calendar separately. In the meantime, please tell me what is the error in the DAX that I have shared?

I do get some difference in the result. I think I need to keep ALLEXCEPT as I will have to use couple of filters. Please let me know your thoughts.