Calculate a year to date without today

DAX DAX Calculations
1

I am looking for a dax formula to calculate the year to date margin % until yesterday. Because in the current day we only have the costs and invoices are made the next day.

The normal DAX formula is this: Marge % = DIVIDE ([Marge], [Omzet],0)

I tried:
Marge % YTD = TOTALYTD(
DIVIDE ([Marge], [Omzet],0),
Datumtabel[Datum],
FILTER(ALL(Datumtabel[Datum], Datumtabel[Datum] <TODAY())))

But that is not working. I imagine it is quite simple, but I can’t get it to work.

Thank you for your help!

2

@JoycevW

IF ( 
    SELECTEDVALUE ( Dates[Date] ) <= TODAY() - 1,
    [Running Total]
)
1 Like
3

So then I first need to make a measure for the running total?

4

Hi @JoycevW,
I share an example applied to the measure [Total Sales], which you can substitute for your measure [Marge %].

You create the following measure:

Total Sales YTD without Today = TOTALYTD( [Total Sales], Dim_Dates[Date], Dim_Dates[Date] < TODAY())

Then you create the measure that the colleague @AntrikshSharma has commented, so that it is calculated only for the days less than today.

Total Sales YTD without Today_Antriksh = IF (
     SELECTEDVALUE ( Dim_Dates[Date] ) <= TODAY() - 1,
     [Total Sales YTD without Today]
)

It also works with the measure:

Total Sales YTD = TOTALYTD( [Total Sales], Dim_Dates[Date] )

Total Sales YTD _Antriksh = IF (
     SELECTEDVALUE ( Dim_Dates[Date] ) <= TODAY() - 1,
     [Total Sales YTD]
)

Although in the latter case, the final totals would not be correct.

image
image891×502 21.3 KB

Regards,

CalculateTYTDwithoutToday_JAFP.pbix (475.0 KB)

1 Like
5

Thank you! This worked like a charm!

1 Like