cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
ansa_naz Member
Member

Running Total for date and sliced category - incorrect dates being returned

 

I need to display a running total by date and category. I should only see dates where there is a category value present. The category can be filtered by report user using a slicer. My tables are:

 

Forecast  
DateQtyJob
01/01/19251
02/02/1962
02/02/1942
26/02/1951
26/02/1931
26/02/1922
12/03/1922

 

Actual  
DateQtyJob
26/01/190.52
15/02/192.51
15/02/1932
15/02/191.752
26/02/1942
12/03/19101
12/03/1911

 

 

Job
Job
1
2

 

 

enter image description here

My data model is:

RunningR.jpg

I want to write a measure to calculate running totals for Forecast.Qty and Actual.Qty, to also take into account a slicer on Job.Job

So if I slice for Job 1, then I need to get:

 

Date      ActualQty1  ForecastQty1
01/01/19  0           25
15/02/19  2.5         25
26/02/19  2.5         33
12/03/19  13.5        33

 

However I am getting all the dates from Dates table being returned, instead of just those with a corresponding value for either Forecast.Qty or Actual.Qty in the underlying tables. See below for what I am currently seeing:

 

Date      ActualQty1  ForecastQty1
01/01/19  0           25
02/01/19 0 25
03/01/19 0 25
...
15/02/19 2.5 25
16/02/19 2.5 25
... 26/02/19 2.5 33 etc

 

My measures are:

 

ActualQty1 = 
var a=
CALCULATE (
    SUM ( 'Actual'[Qty] ),
    FILTER (    ALL(Dates),'Dates'[Date]<=MAX('Dates'[Date]))
    )
return IF(ISBLANK(a),0,a)
ForecastQty1 = 
var a=
CALCULATE (
    SUM ( 'Forecast'[Qty] ),
    FILTER (    ALL(Dates),'Dates'[Date]<=MAX('Dates'[Date]))
    )
    return IF(ISBLANK(a),0,a)

Is there any way to return only those dates where the Job selected has a value for either Forecast.Qty or Actual.Qty?

My PBIX file is here:

 

https://1drv.ms/u/s!AuiIgc_S9J5JhbYoRL4pXaQYHeWHgQ

 

Many thanks for all help

1 ACCEPTED SOLUTION

Accepted Solutions
ansa_naz Member
Member

Re: Running Total for date and sliced category - incorrect dates being returned

Just managed to get the solution:

 

ActualQty = 
var a=
CALCULATE (
    SUM ( 'Actual'[Qty] ),
    FILTER (    ALL(Dates),'Dates'[Date]<=MAX('Dates'[Date]))
    )
RETURN
IF (
    calculate(SUM ( 'Actual'[Qty] )) + calculate(SUM ( 'Forecast'[Qty] ))
        = BLANK (),
    BLANK (),
    IF (
        a = BLANK (),
        0,
        a
    )
)

Thanks for help

View solution in original post

3 REPLIES 3
Community Support Team
Community Support Team

Re: Running Total for date and sliced category - incorrect dates being returned

@ansa_naz ,

 

Modify your measures using DAX below:

ActualQty1 = 
var a=
CALCULATE (
    SUM ( 'Actual'[Qty] ),
    FILTER (    ALL(Dates),COUNTROWS(FILTER('Dates', 'Dates'[Date] >EARLIER('Dates'[Date]))))
    )
return IF(ISBLANK(a),0,a)

ForecastQty1 = 
var a=
CALCULATE (
    SUM ( 'Forecast'[Qty] ),
    FILTER (    ALL(Dates),COUNTROWS(FILTER('Dates', 'Dates'[Date] > EARLIER('Dates'[Date]))))
    )
    return IF(ISBLANK(a),0,a)

Community Support Team _ Jimmy Tao

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

ansa_naz Member
Member

Re: Running Total for date and sliced category - incorrect dates being returned

Hi @v-yuta-msft  I have just tried these measures but I am still getting all dates from Dates.Date table being returned, not just the dates with a Quantity value for the job sliced:

 

Running.jpg

 

I would expect the below when slicing for Job 1 instead of the above:

 

DateActualQty2ForecastQty2
01/01/19025
15/02/192.525
26/02/192.533
12/03/1913.533

 

Can you provide any further guidance please?

ansa_naz Member
Member

Re: Running Total for date and sliced category - incorrect dates being returned

Just managed to get the solution:

 

ActualQty = 
var a=
CALCULATE (
    SUM ( 'Actual'[Qty] ),
    FILTER (    ALL(Dates),'Dates'[Date]<=MAX('Dates'[Date]))
    )
RETURN
IF (
    calculate(SUM ( 'Actual'[Qty] )) + calculate(SUM ( 'Forecast'[Qty] ))
        = BLANK (),
    BLANK (),
    IF (
        a = BLANK (),
        0,
        a
    )
)

Thanks for help

View solution in original post

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)