Running Total for two categories - filling in the blanks

[ansa_naz]

Hi all

I have the following tables:

Forecast:

Date               Qty
01/01/2019   25
26/02/2019   5

Actual:

Date               Qty
15/02/2019   2.50
12/03/2019   10

My data model is:

DatesTable 1---->M Forecast
DatesTable 1---->M Actual

How do I create a measure for ForecastQty and ActualQty which shows a running total for all the dates in each table?

So I would get:

Date                         ForecastQty               ActualQty
01/01/2019              25                              0
15/02/2019             25                               2.50
26/02/2019             30                               2.50
12/03/2019             30                              12.50

I have tried the following:

ActualQty =
CALCULATE (
SUM ( 'Actual'[Qty] ),
FILTER (
ALLSELECTED ( 'Actual' ),
'Actual'[Date] <= MAX ( Actual[Date] )
)
)

ForecastQty =
CALCULATE (
SUM ( 'Forecast'[Qty] ),
FILTER (
ALLSELECTED ( 'Forecast' ),
'Forecast'[Date] <= MAX ( Forecast[Date] )
)
)

However this gives me:

Date                      ForecastQty              ActualQty
01/01/2019           25
15/02/2019                                            2.50
26/02/2019          30
12/03/2019                                            12.50

Example PBIX is here:

PowerBI example file

Many thanks for all help

[v-danhe-msft · Accepted solution]

Hi @ansa_naz ,

You could use UNION function to create a new table:

New Table = UNION(Actual,'Forecast')

Modify your relationship and measure:

ActualQty = var a=
CALCULATE (
    SUM ( 'Actual'[Qty] ),
    FILTER (
        ALL ( 'New Table'),
    'New Table'[Date]<=MAX('New Table'[Date]))
)
return IF(ISBLANK(a),0,a)

ForecastQty = 
CALCULATE (
    SUM ( 'Forecast'[Qty] ),
      FILTER (
        ALL ( 'New Table'),
    'New Table'[Date]<=MAX('New Table'[Date]))
    )

Result:

You could also download the pbix file to have a view.

Regards,

Daniel He

[v-danhe-msft · Accepted solution]

Hi @ansa_naz ,

You could use UNION function to create a new table:

New Table = UNION(Actual,'Forecast')

Modify your relationship and measure:

ActualQty = var a=
CALCULATE (
    SUM ( 'Actual'[Qty] ),
    FILTER (
        ALL ( 'New Table'),
    'New Table'[Date]<=MAX('New Table'[Date]))
)
return IF(ISBLANK(a),0,a)

ForecastQty = 
CALCULATE (
    SUM ( 'Forecast'[Qty] ),
      FILTER (
        ALL ( 'New Table'),
    'New Table'[Date]<=MAX('New Table'[Date]))
    )

Result:

You could also download the pbix file to have a view.

Regards,

Daniel He

[ansa_naz]

Hi @v-danhe-msft  many thanks for your reply. Is it possible to do this using a measure instead of a UNION table? As I have other columns in each of the two tables in UION function, and they are not the same column in each table. Many thanks for your help

[ansa_naz]

Just figured it out, I can use a SELECTCOLUMNS statement after the UNION to select only the columns I need. Thanks @v-danhe-msft !

---

---

[ansa_naz]

Hi @v-danhe-msft just realised this doesnt work, I have another issue. My full model is currently below and also linked:

Dates 1 ---> M Forecast

Dates 1 ---> M Actual

Job  1 ---> M  Forecast

Job 1 --->M  Actual

Using the method you suggested, by adding a new Dates table into the model, I am returning data for dates where there is no change. This is because Forecast and Actual also have a further column, Job. I only want to return a running total for a filtered (sliced) Job. Using your method, if I slice for Job 2, I am getting:

But I actually need:

Date             ActualQty1       ForecastQty1

26/01/19      0.50                    0

02/02/19      0.50                  10

15/02/19       5.25                  10

26/02/19        9.25                 12

12/03/19        9.25                  14

See below PBI file:

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

Any ideas how I can accomplish that? Many thanks

[ansa_naz · Accepted solution]

Also, if I slice for Job 1, then I am currently getting:

However, I need:

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

Cheers for all help

[v-danhe-msft]

Hi @ansa_naz ,

Could you please mark the first issue and re-open your new issue to get more help?

Regards,

Daniel He