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

[ansa_naz]

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
Date	Qty	Job
01/01/19	25	1
02/02/19	6	2
02/02/19	4	2
26/02/19	5	1
26/02/19	3	1
26/02/19	2	2
12/03/19	2	2

Actual
Date	Qty	Job
26/01/19	0.5	2
15/02/19	2.5	1
15/02/19	3	2
15/02/19	1.75	2
26/02/19	4	2
12/03/19	10	1
12/03/19	1	1

Job

Job

1

2

My data model is:

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

[v-yuta-msft]

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

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:

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

Date	ActualQty2	ForecastQty2
01/01/19	0	25
15/02/19	2.5	25
26/02/19	2.5	33
12/03/19	13.5	33

Can you provide any further guidance please?

[ansa_naz · Accepted solution]

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