cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Cobra77 Member
Member

Fix end date and begin date dynamically on chart with measure

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.

2 REPLIES 2
Community Support Team
Community Support Team

Re: Fix end date and begin date dynamically on chart with measure

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 Member
Member

Re: Fix end date and begin date dynamically on chart with measure

 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

 

graphic.jpg

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 6 members 932 guests
Please welcome our newest community members: