Return the Index of today

Kev59 — Tue, 02 Apr 2024 15:12:00 GMT

Dear all

First, sorry for my poor English level.

A quick explanation of my problem:

I have a dataset based on a date table (calendar) but for all my visuals/calculations I use another table named 'calendar Fiscal' (both are linked on the date field).

I have a problem when I try to calculate the revenues for the last 12 months for example (except the current one).

If I use the following code:

Last 12 Months = VAR DateStart = EDATE ( EOMONTH ( TODAY (), -1 ), -12 ) + 1 VAR DateEnd = TODAY () return CALCULATE( switch( TRUE(), SELECTEDVALUE(_Metric[_Metric Commande])=0,SUM('CDD - Invoiced Sales'[Quantity in Base UOM]), SELECTEDVALUE(_Metric[_Metric Commande])=1,SUM('CDD - Invoiced Sales'[Net Sales USD]) ), DATESBETWEEN('Calendar Fiscal'[TFS Date],DateStart,DateEnd) )

Both Var are based on my calendar table and not my calendar Fiscal table... so I have a difference between the value calculated and the real one (because my Fiscal Month aren't fiting the normal one):

ex: Fiscal March Month begins on 3rd March and ends on 30rd March.

To bypass this problem I have created an Index on my fiscal calendar based on the column YYYY-MM and I would like to use it in my calculation.

I tried this code:

Last 12 Months test = Var MaxIndex = MAX('Calendar Fiscal'[Index]) Var MinIndex = MaxIndex - 12 Var Result = CALCULATE( switch( TRUE(), SELECTEDVALUE(_Metric[_Metric Commande])=0,SUM('CDD - Invoiced Sales'[Quantity in Base UOM]), SELECTEDVALUE(_Metric[_Metric Commande])=1,SUM('CDD - Invoiced Sales'[Net Sales USD]) ), FILTER(ALL('Calendar Fiscal'), 'Calendar Fiscal'[Index] <= MaxIndex && 'Calendar Fiscal'[Index] > MinIndex) ) return Result

but it's not good for the variable MaxIndex....

I would like to return the 'calendar Fiscal' [Index ] of previous month of the current day (today) but I don't know how to do that

ps: I'm not an expert of PowerBI 😉

many thanks in advance

Re: Return the Index of today

Anonymous — Wed, 03 Apr 2024 04:35:52 GMT

Hi, Kev59

Depending on your description, you can try the following DAX expressions:

Last 12 Months Fiscal = VAR CurrentDate = TODAY () VAR FiscalTodayIndex = CALCULATE ( MAX ( 'Calendar Fiscal'[Index] ), FILTER ( 'Calendar Fiscal', 'Calendar Fiscal'[Date] = CurrentDate ) ) VAR StartIndex = FiscalTodayIndex - 12 VAR EndIndex = FiscalTodayIndex - 1 RETURN CALCULATE ( SWITCH ( TRUE (), SELECTEDVALUE ( _Metric[_Metric Commande] ) = 0, SUM ( 'CDD - Invoiced Sales'[Quantity in Base UOM] ), SELECTEDVALUE ( _Metric[_Metric Commande] ) = 1, SUM ( 'CDD - Invoiced Sales'[Net Sales USD] ) ), FILTER ( ALL ( 'Calendar Fiscal' ), 'Calendar Fiscal'[Index] > StartIndex && 'Calendar Fiscal'[Index] <= EndIndex ) )

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

topic Return the Index of today in DAX Commands and Tips

Return the Index of today

Re: Return the Index of today