Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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.
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])
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |