Conditional Calculation

[mtb1973]

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

[v-xicai · Accepted solution]

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.

[Greg_Deckler]

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])