Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ansa_naz
Continued Contributor
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
v-danhe-msft
Employee
Employee

Hi @ansa_naz ,

You could use UNION function to create a new table:

New Table = UNION(Actual,'Forecast')

1.PNG

Modify your relationship and measure:

2.PNG

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:

3.PNG

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

 

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.

View solution in original post

ansa_naz
Continued Contributor
Continued Contributor

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

 

Running1.jpg

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

View solution in original post

6 REPLIES 6
v-danhe-msft
Employee
Employee

Hi @ansa_naz ,

You could use UNION function to create a new table:

New Table = UNION(Actual,'Forecast')

1.PNG

Modify your relationship and measure:

2.PNG

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:

3.PNG

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

 

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.

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
Continued Contributor
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 !



 

ansa_naz
Continued Contributor
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

 

Running.jpg

 

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:

 

Running1.jpg

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
Continued Contributor
Continued Contributor

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

 

Running1.jpg

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

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.