Dax Formula for YTD Calculation

[kelvin-lkhca]

Dear All Expert,

I create a Dax formula to sum up the number of the periods selected to a YTD Revenue, EBIT and Margin. However, the YTD in the matrix table does not product the number. Can you please help to advise what goes wrong with the DAX formula?

I have shared the PBI file with the link below. Appreciate the solution from you in advance!

https://drive.google.com/file/d/1DfHcyslQ9DcRUlk5emR0LeM5bm-NlSmO/view?usp=sharing

[Ashish_Mathur]

Hi,

PBI file attached.

[kelvin-lkhca]

Hi Ashish Mathur,

Thanks for the proposed solution. Unfortunately, I can only present the Revenue (after elim), EBITxc1 and margin under 1 measure due to certain constraint. How do I get the YTD by tweaking the Dax formula in the original measure of my PBI file?

[gmsamborn]

Hi @kelvin-lkhca 

Can you explain why the solution supplied by @Ashish_Mathur  doesn't work?  I can't see anything wrong.

If you have to "tweak" your measure, you'll have to fix a couple of things.

1) Your SWITCH statement is incorrect.  It never makes it down to the YTD calculations since it matches an earlier option.  I made a change to have all options check if Month is = OR <> to "YTD".

2) Your calculations for your _All variables are incorrect.  You can't use a variable like that in a CALCULATE statement.  Variables (actually constants) are only set once - not repeatedly like you are trying to recalculate it.  If you were using a measure like that, it would work.  (I hope that makes sense.)

I made those changes to your measure but still have a problem with the _All calulations.  (I probably won't be able to do much more until tomorrow.)

Let me know if the above doesn't make sense.

Measure = 
VAR EBITxc1 =
    CALCULATE(
        [M_Sum],
        Fact_Data[Account] = "EBIT"
    )
VAR Revenue_Elim =
    CALCULATE(
        [M_Sum],
        Fact_Data[Account] = "Revenue"
    )
VAR EBITxc1Margin =
    FORMAT(
        DIVIDE(
            EBITxc1,
            Revenue_Elim
        ),
        "#,##.0%;-#,##.0%"
    )
VAR EBITxc1_All = // Changed but still not working **********************
    CALCULATE(
        [M_Sum],
        Fact_Data[Account] = "EBIT",
        ALLSELECTED( Period_YTD_Display[Month] )
    )
VAR Revenue_Elim_All = // Changed but still not working **********************
    CALCULATE(
        [M_Sum],
        Fact_Data[Account] = "Revenue",
        ALLSELECTED( Period_YTD_Display[Month] )
    )
VAR EBITxc1Margin_All =
	FORMAT(
		CALCULATE(
			DIVIDE(
			    CALCULATE(
                    [M_Sum],
                    Fact_Data[Account] = "EBIT"
                ),
			    CALCULATE(
                    [M_Sum],
                    Fact_Data[Account] = "Revenue"
                )
			),
			ALLSELECTED( Period_YTD_Display[Month] )
		),
		"#,##.0%;-#,##.0%"
	)
VAR Account = SELECTEDVALUE( Account_Display_Seq[Account Description] )
VAR Period = SELECTEDVALUE( Period_YTD_Display[Month] )
VAR Output =
    SWITCH(
        TRUE(),
        Account = "EBITxc1 margin"
            && Period <> "YTD", 
            EBITxc1Margin,
        Account = "EBITxc1"
            && Period <> "YTD", 
            FORMAT( EBITxc1, "#,###;-#,###" ),
        Account = "Revenue (after elim)"
            && Period <> "YTD", 
            FORMAT( Revenue_Elim, "#,###;-#,###" ),
        Account = "EBITxc1 margin"
            && Period = "YTD", 
            EBITxc1Margin_All,
        Account = "EBITxc1"
            && Period = "YTD",
            FORMAT( EBITxc1_All, "#,###;-#,###" ),
        Account = "Revenue (after elim)"
            && Period = "YTD",
            FORMAT( Revenue_Elim_All, "#,###;-#,###" )
    )
RETURN
    Output

[kelvin-lkhca]

Hi Gmsamborn,

@gmsamborn 

This is because in my real data, I will have to put up a slicer which allow user to choose the account to show as below. By using the solution provided by @Ashish_Mathur . The account which are filtered out will still be remained in the matrix table because they are from different measure. Whereas by using the concept of my measure(although the formula is not correct), only the account being selected will be shown in the matrix table. 

How my explanation above can justify why the proposed solution by @Ashish_Mathur  is good but still not fully solve the problem I am facing.

[lbendlin]

Please use the "Quick Measures"  feature in Power BI. It includes an example for YTD calculation.