Return the Index of today

[Kev59]

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

[Anonymous · Accepted solution]

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.