cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Continued Contributor

## Running Total for two categories - filling in the blanks

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

2 ACCEPTED SOLUTIONS
Employee

Hi @ansa_naz ,

You could use UNION function to create a new table:

New Table = UNION(Actual,'Forecast')

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:

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Continued Contributor

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

6 REPLIES 6
Employee

Hi @ansa_naz ,

You could use UNION function to create a new table:

New Table = UNION(Actual,'Forecast')

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:

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Continued Contributor

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

Continued Contributor

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

Continued Contributor

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

Continued Contributor

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

Employee

Hi @ansa_naz ,

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

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.