cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
Highlighted
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
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors