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 am working on a Power BI Report (which is in direct query mode with SSAS tabular model as source). I need to create two measures opening base and closing base with below condition.
opening base should be show value only for the minimum date range chosen by the user in the date slicer.
closing base should be show value only for the maximum date range chosen by the user in the date slicer.
For e.g : if user chooses between may 1, 2020 and may 17,2020 opening base will show value only for may 1, 2020 & closing base show value only for may 17th,2020. For other dates value should be 0 . I have
Created below measures to get the minimum date of the date slicer.
StartDate = CALCULATE(min(‘DATE’[DATE]),ALLSELECTED(‘DATE’[DATE]))
OP = if(SELECTEDVALUE(‘DATE’[DATE]) = [StartDate], CALCULATE(sum(MEASUREMENTS[OPENING_BASE_VAL]),DATESBETWEEN(‘DATE’[DATE],[StartDate],[StartDate])),0)
Now i can get the desired output, but the grand-total is become 0 as shown in below picture. any help much appreciated.
Solved! Go to Solution.
@baijumohan1990 , Either change if to a filter. Or have a formula like one given below
I have used your existing measure in this
OP new=
sumx(summarize( Date, Date[Date],"_1",[OP]),[_1])
Hi @baijumohan1990 ,
In general, the measure is like @ amitchandak mentioned, since I'm not certain your dataset, I have created this measure:
OP =
VAR tab =
SUMMARIZE (
MEASUREMENTS,
MEASUREMENTS[Date],
MEASUREMENTS[OPENING_BASE_VAL],
"Re",
VAR _date = MEASUREMENTS[Date]
RETURN
IF (
_date IN FILTERS ( 'Date'[Date] ),
SUM ( MEASUREMENTS[OPENING_BASE_VAL] ),
0
)
)
RETURN
SUMX ( tab, [Re] )
My sample file is attached, please check and refer under your actual situation: Measure with Sum using Calculate and IF not showing totals correctly.pbix
Best Regards,
Yingjie Li
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
@baijumohan1990 , Either change if to a filter. Or have a formula like one given below
I have used your existing measure in this
OP new=
sumx(summarize( Date, Date[Date],"_1",[OP]),[_1])
hi amit,
thanks for your reply. i will try this out. could you plese give an example to replace if with filter? Also in the op_new measure you have created -1 is some placeholder?
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 |