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
HansBaeten
Frequent Visitor

Combine Budget and Actual data based on a date

Hi,

 

In my datamodel, I currently have 2 fact tables: FactBudget (containing budget data) and FactActuals (containing actual data). Both fact tables are linked to a date dimension.

 

Both tables can contain data for a same month. For example: it's possible to have budget data for August in FactBudget and  actuals for the same month in FactActuals.

 

I don't want to count both budget and actuals together.

 

My goal is to define the max date of my actuals, for example I have actuals until today September 18th.

 

In my report I want to see actuals until september (september included) and budget data as from october.  

 

When actuals until october, I want to see budget data as from november.

 

How can I do this?

 

Link to some sample data: https://drive.google.com/open?id=1oADG-gV7Sle5CT6q5XMw6IfYGQHM9VK4

 

Thanks for the help.

 

Regards,

Hans

1 ACCEPTED SOLUTION

Hi @HansBaeten ,

you have to remove the filters from the DimTable that holds the streams as well like so:

 

CombinedAmountR =
VAR MonthEndActuals =
    CALCULATE (
        MAX ( FactActual[DateId] ),
        REMOVEFILTERS ( DimDate ),
        REMOVEFILTERS ( DimStreams )
    )
VAR MaxDate =
    ENDOFMONTH (
        CALCULATETABLE (
            VALUES ( DimDate[FullDateKey] ),
            DimDate[DateId] <= MonthEndActuals
        )
    )
VAR Result =
    IF (
        MAX ( DimDate[FullDateKey] ) <= MaxDate,
        SUM ( FactActual[Amount] ),
        SUM ( FactBudget[Amount] )
    )
RETURN
    Result

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

7 REPLIES 7
smpa01
Super User
Super User

@HansBaeten  my approach was following

 

A. create a distinct(composite) table with all the ids from the Actual and Budget table

Composite_Tbl = 
VAR _ACT=SELECTCOLUMNS
                       (
                           FactActual,
                                  "DateId",FactActual[DateId],
                                  "CompanyId",FactActual[CompanyId],
                                  "StreamId",FactActual[StreamId],
                                  "StreamItemId",FactActual[StreamItemId],
                                  "BusinessUnitId", FactActual[BusinessUnitId]
                       )
VAR _BUDGET=SELECTCOLUMNS
                       (
                           FactBudget,
                                  "DateId",FactBudget[DateId],
                                  "CompanyId",FactBudget[CompanyId],
                                  "StreamId",FactBudget[StreamId],
                                  "StreamItemId",FactBudget[StreamItemId],
                                  "BusinessUnitId", FactBudget[BusinessUnitId]
                       )                 
VAR _Union = DISTINCT
                     (
                       UNION(_ACT,_BUDGET)
                     )
                                  
RETURN _Union

B. Create two separate summarized tables (M equivalent group by) one each for actual and budget to be utilised for lookup later.

 

 Actual_Total returns a subtotal table from Fact Actual Table by DateId, CompanyId,StreamId,StreamItemId,BusinessUnitId.

 

Budget_Total returns a subtotal table from Fact Budget Table by DateId, CompanyId,StreamId,StreamItemId,BusinessUnitId which is further filtered to contain only succedding months (9,10,11,12) after the max month (8) contained in the Fact Actual table.

 

Actual_Total = SUMMARIZE
                       (
                         FactActual,
                         FactActual[DateId],FactActual[CompanyId],FactActual[StreamId],FactActual[StreamItemId],FactActual[BusinessUnitId],
                         "Actual",SUM(FactActual[Amount])
                        )

 

Budget_Total = FILTER(
                        SUMMARIZE
                              (FactBudget,
                                   FactBudget[DateId],FactBudget[CompanyId],FactBudget[StreamId],FactBudget[StreamItemId],FactBudget[BusinessUnitId],
                                   "Budget",SUM(FactBudget[Amount])
                               ),
                               LOOKUPVALUE(DimDate[Month],DimDate[DateId],FactBudget[DateId])>LOOKUPVALUE(DimDate[Month],DimDate[DateId],MAX(FactActual[DateId])))

C. Create calculated column as following in Composite Table

Actual = LOOKUPVALUE(Actual_Total[Actual],
                                          Actual_Total[DateId],[DateId],
                                          Actual_Total[CompanyId],[CompanyId],
                                          Actual_Total[StreamId],[StreamId],
                                          Actual_Total[StreamItemId],[StreamItemId],
                                          Actual_Total[BusinessUnitId],[BusinessUnitId]
                    )

Budget = LOOKUPVALUE(Budget_Total[Budget],
                                          Budget_Total[DateId],[DateId],
                                          Budget_Total[CompanyId],[CompanyId],
                                          Budget_Total[StreamId],[StreamId],
                                          Budget_Total[StreamItemId],[StreamItemId],
                                          Budget_Total[BusinessUnitId],[BusinessUnitId]
                    )
Single_AMTValue = IF(ISBLANK([Actual]),[Budget],[Actual])

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
Anonymous
Not applicable

 

 

Sales Blank= 
IF ( ( CALCULATE ( SUM ( 'TB Actual'[MTD] ), 'TB Actual'[Type] = "S" ) ) = 0, BLANK (), IF ( DAY ( TODAY () ) < 20, ( CALCULATE ( SUM ( 'TB Actual'[MTD] ), 'TB Actual'[Type] = "S", FILTER ( DateTable, DateTable[Date] < EOMONTH ( TODAY (), -1 ) ) ) ), CALCULATE ( SUM ( 'TB Actual'[MTD] ), 'TB Actual'[Type] = "S" ) ) )

 

 

What this does:

If Actual Sales have not happened yet, leave the value for that month blank (instead of 0 so the visualizations scale better). Also, if we are not past the 20th day of the month (because the smaller sales values mess up the scale of the visuals until they have some significant value) then just show me the sales through the end of last month, otherwise show me the sales up through today.

 

You can then apply the inverse date logic to your budget info. Something like: if "Sales Blank" is blank, then show sum of budget values.

 

 

 

 

Hi @HansBaeten ,

understood.

This measure should work then:

 

Budget = 
VAR MonthEndActuals = CALCULATE(MAX(FactActual[DateId]), ALL(DimDate))
VAR MaxDate = ENDOFMONTH(CALCULATETABLE(VALUES(DimDate[FullDateKey]), DimDate[DateId] <= MonthEndActuals))
VAR Result = if(MAX(DimDate[FullDateKey]) <= MaxDate, SUM(FactActual[Amount]), Sum(FactBudget[Amount]))
RETURN
Result

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Hi @ImkeF ,

 

it almost works, but there is an issue when looking on row level:

 

Knipsel.JPG

 

For example: only the stream TRADE CREDITORS has an actual value in September (combinedAmount is based on your measure). For all other streams I only have budget values (value in CombinedAmount is equal to values in Budget).

In this example, all other streams should be empty. If there is at least €1 actual in month X, I only want to see the actuals for that month (for all streams, streamitems,...) even if there are no actuals for a specific stream, item,...

 

Is there a way to solve this in your measure?

 

Regards,

Hans

Hi @HansBaeten ,

you have to remove the filters from the DimTable that holds the streams as well like so:

 

CombinedAmountR =
VAR MonthEndActuals =
    CALCULATE (
        MAX ( FactActual[DateId] ),
        REMOVEFILTERS ( DimDate ),
        REMOVEFILTERS ( DimStreams )
    )
VAR MaxDate =
    ENDOFMONTH (
        CALCULATETABLE (
            VALUES ( DimDate[FullDateKey] ),
            DimDate[DateId] <= MonthEndActuals
        )
    )
VAR Result =
    IF (
        MAX ( DimDate[FullDateKey] ) <= MaxDate,
        SUM ( FactActual[Amount] ),
        SUM ( FactBudget[Amount] )
    )
RETURN
    Result

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

ImkeF
Super User
Super User

Hi @HansBaeten 

To me this sounds as if you want to pull your Budget figures one month forward. This would be the necessary DAX-code:

 

Budget = 
CALCULATE(SUM(FactBudget[Amount]), DATEADD(DimDate[FullDateKey], +1,MONTH))

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Hi @ImkeF ,

 

no not really. I don't want to push the budget data 1 month forward.

 

I want to have 1 "combined" amount: if actuals available for month X, take the actuals for this month, otherwise take the budget amount for this month.

 

Hope this clarifies my issue?

 

Regards,

Hans

 

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.