cancel
Showing results for 
Search instead for 
Did you mean: 
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

Accepted Solutions
Super User III
Super User III

Re: Combine Budget and Actual data based on a date

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

 

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

Imke Feldmann

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
Super User III
Super User III

Re: Combine Budget and Actual data based on a date

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))
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark my post as a solution. Thanks!

Imke Feldmann

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

 
HansBaeten
Frequent Visitor

Re: Combine Budget and Actual data based on a date

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

 

svoves Resolver I
Resolver I

Re: Combine Budget and Actual data based on a date

 

 

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.

 

 

 

 

Super User III
Super User III

Re: Combine Budget and Actual data based on a date

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
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark my post as a solution. Thanks!

Imke Feldmann

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

 
Super User I
Super User I

Re: Combine Budget and Actual data based on a date

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

 

HansBaeten
Frequent Visitor

Re: Combine Budget and Actual data based on a date

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

Super User III
Super User III

Re: Combine Budget and Actual data based on a date

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

 

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

Imke Feldmann

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

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors