Dax formula for totals

DAX DAX Calculations
1

I´m having an issue trying to change the context for totals in a dax calculation. The scenario is simple, every month we update the forecast for the year. In January we forecast 12 months, in feb 11 months and so on. The scenario would be something like this:
image

The issue arises with totals, as we update the forecast some data are consolidated and thus the “correct” total should read as stated in the image above.

With this formula I´m able to retrieve the last forecast

=CALCULATE([ValorFsct];LASTDATE(Tabla1[FechaPronostico]))

but I´m not able to make the total right. I´m sharing the excel file with you to see if someone can help with the issue.

Excel file link

Thanks in advance for your help.

2

Hi David I don’t use Powerpivot, can we get this into Power BI.

Have you been able to review the techniques showcased here in these links. These show the overall technique to use in many of these situations.

See here

3

Hi Sam, it´s my pleasure to contact you again, thanks for your support and the great site you have.

I´ve created a pbix version with the same data

Pbix file

image
image931×513 28.1 KB

I had seen the video “Getting totals correct” yesterday, in fact I tried to replicate the scenario but i wasn´t able to do so, i get lost with the summarize var. I´ve tried some other aproachs but failing to do so too.

4

Doesn’t seem to let me download it unfortunately. You can email it to me.

So I see you have a table with the correct values in it now? Have you work it out or is that just a dummy table.

The way to think about totals is to imagine no context. So no filter are applied.

That matrix actually makes it a little more difficult for me to imagine so I need to test it.

Send me an email with the file.

Chrs

5

Ok, sorry for the delay.

This should get you what you need.

LastFsct = 
VAR ForecastTotal = CALCULATE( [Sales Forecast], LASTDATE(Tabla1[Forecast Date] ))

RETURN
IF( ISFILTERED( Tabla1[DimForecast] ),
    ForecastTotal,
        SUMX( SUMMARIZE( Tabla1, Tabla1[DimForecast],
                "Forecasts", CALCULATE( [Sales Forecast], LASTDATE(Tabla1[Forecast Date] )) ),
                    [Forecasts] ) )

It’s a little trickier than normal, but this is some new tricks I use in these sort of situations.

I think it looks relatively clean as a formula.

image
image.png826×640 26.1 KB

Hopefully this works for you also.

Chrs

1 Like
6

it worked perfectly, thanks again for the great solution.