Fix end date and begin date dynamically on chart with measure

[Cobra77]

Hi,

I want fix begin and end date of a chart dynamically in Power bi , with measure in SSAS tabular.

For the end its ok with : https://community.powerbi.com/t5/Desktop/Fix-end-date-dynamically-on-chart-with-measure/td-p/356679

We recover the last date in a fact table. ( ex actually its Avril 2018 )

it will be the end date of the graph

and from this date posted the last 12 months 

( from May 2017 to Avril 2018 )

I try with :

VAR LastDat2 = LASTNONBLANK( '01 - Calendrier'[04 - Date]; COUNTROWS (RELATEDTABLE('92 - HT')))
VAR LastDat = LASTDATE ('01 - Calendrier'[04 - Date] )
VAR LastDat12 =  DATEADD(LastDat2;-12; MONTH)

RETURN IF (  LastDat2 >= FIRSTDATE ('01 - Calendrier'[04 - Date] ) && MAX( '01 - Calendrier'[04 - Date] ) >= LastDat12 ;
 CALCULATE([TF]; ALL('01 - Calendrier'[04 - Date]);  DATESBETWEEN (
          '01 - Calendrier'[04 - Date];
      NEXTDAY ( SAMEPERIODLASTYEAR ( LastDat ) );
        LastDat )
    ))

Thanks for your help.

[v-yuta-msft]

Hi Cobra77,

Modify DAX like this and try again:

Result =
VAR LastDat2 =
    LASTNONBLANK (
        '01 - Calendrier'[04 - Date],
        CALCULATE (
            COUNTROWS ( RELATEDTABLE ( '92 - HT' ) ),
            ALLEXCEPT ( '01 - Calendrier', '01 - Calendrier'[04 - Date] )
        )
    )
VAR LastDat =
    LASTDATE ( '01 - Calendrier'[04 - Date] )
VAR LastDat12 =
    DATEADD ( LastDat2, -12, MONTH )
RETURN
    IF (
        LastDat2 >= FIRSTDATE ( '01 - Calendrier'[04 - Date] )
            && ( MAX ( '01 - Calendrier'[04 - Date] ) >= LastDat12 ),
        CALCULATE (
            [TF],
            ALL ( '01 - Calendrier'[04 - Date] ),
            DATESBETWEEN (
                '01 - Calendrier'[04 - Date],
                NEXTDAY ( SAMEPERIODLASTYEAR ( LastDat ) ),
                LastDat
            )
        )
    )

Regards,

Jimmy Tao

[Cobra77]

 Hi

Thanks,  but it does not work either.

i also tried  try with : ( brcause tf its  measure who divise 2 measures on 2 fact tables )

VAR LastDat2 = LASTNONBLANK( '01 - Calendrier'[04 - Date];CALCULATE ( [tf];ALLEXCEPT ( '01 - Calendrier';'01 - Calendrier'[04 - Date] )))

I try to filter too on axe power bi desktop with Top N ( AnneMoisNum = 201804,201803 etc ... in date dimension ),  but values disappear