Forum Discussion

mtb1973's avatar
mtb1973
Frequent Visitor
6 years ago
Solved

Conditional Calculation

I just received a requirement where the requestor has asked if I could add a cutoff date to specific calculations.
In this particular case, the request involves calculating waste % two different ways based on what the date range is.

If the dates are prior to August 1st 2019, the user wants to use meaure A otherwise measure B. Now I am running into an issue where I want to make sure it calculates properly when the date range includes both dates prior to 8/1/2019 and after 8/1/2019. This would need logic that combines the two. Any help would be great. Thanks

Need Help
  • v-xicai's avatar
    v-xicai
    6 years ago

    Hi mtb1973 ,

     

    Assuming the Date range is changed according the date slicer, you can create measure like DAX below.

     

    Measure1 =
VAR FromDate =
    CALCULATE ( MIN ( Slicer[Date] ), ALLSELECTED ( Slicer[Date] ) )
VAR ToDate =
    CALCULATE ( MAX ( Slicer[Date] ), ALLSELECTED ( Slicer[Date] ) )
RETURN
    SWITCH (
        TRUE (),
        ToDate <= DATE ( 2019, 8, 1 ), [MeasureA],
        FromDate > DATE ( 2019, 8, 1 ), [MeasureB],
        FromDate < DATE ( 2019, 8, 1 )
            && ToDate > DATE ( 2019, 8, 1 ), [MeasureA] & " " & [MeasureB]
    )

     

    If I misunderstood it, could you please share your sample data and desired output screenshots for further analysis? You can also upload sample pbix to OneDrive and post the link here. Do mask sensitive data before uploading.

     

    Best Regards,

    Amy

     

    Community Support Team _ Amy

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

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    Some of this depends. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

     

    That being said, you could create a third measure that is something like:

     

    Measure 3 = IF(MAX([Date] < DATE(2019,8,1), [Measure 1], [Measure 2])

     

    Then perhaps you could do something like:

     

    Measure 4 = 
  VAR __Table = ADDCOLUMNS( 'Table',"__Value",[Measure 3])
RETURN
  SUMX(__Table,[Value])

     

  • v-xicai's avatar
    v-xicai
    Community Support
    6 years ago

    Hi mtb1973 ,

     

    Assuming the Date range is changed according the date slicer, you can create measure like DAX below.

     

    Measure1 =
VAR FromDate =
    CALCULATE ( MIN ( Slicer[Date] ), ALLSELECTED ( Slicer[Date] ) )
VAR ToDate =
    CALCULATE ( MAX ( Slicer[Date] ), ALLSELECTED ( Slicer[Date] ) )
RETURN
    SWITCH (
        TRUE (),
        ToDate <= DATE ( 2019, 8, 1 ), [MeasureA],
        FromDate > DATE ( 2019, 8, 1 ), [MeasureB],
        FromDate < DATE ( 2019, 8, 1 )
            && ToDate > DATE ( 2019, 8, 1 ), [MeasureA] & " " & [MeasureB]
    )

     

    If I misunderstood it, could you please share your sample data and desired output screenshots for further analysis? You can also upload sample pbix to OneDrive and post the link here. Do mask sensitive data before uploading.

     

    Best Regards,

    Amy

     

    Community Support Team _ Amy

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