cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Measure with Sum using Calculate and IF not showing totals correctly

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.

 

Captures.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IV
Super User IV

Re: Measure with Sum using Calculate and IF not showing totals correctly

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

View solution in original post

3 REPLIES 3
Highlighted
Super User IV
Super User IV

Re: Measure with Sum using Calculate and IF not showing totals correctly

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

View solution in original post

Highlighted
Frequent Visitor

Re: Measure with Sum using Calculate and IF not showing totals correctly

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?

Highlighted
Microsoft
Microsoft

Re: Measure with Sum using Calculate and IF not showing totals correctly

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

result.png

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.

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors