Forum Discussion

kelvin-lkhca's avatar
kelvin-lkhca
Icon for Helper I rankHelper I
2 years ago

Dax Formula for YTD Calculation

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

Need Help

5 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years ago

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

    • kelvin-lkhca's avatar
      kelvin-lkhca
      Icon for Helper I rankHelper I
      2 years ago

      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's avatar
        gmsamborn
        Icon for Super User rankSuper User
        2 years ago

        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