Forum Discussion

jpereztang's avatar
jpereztang
Helper I
10 years ago
Solved

Get period context

Hi everyone,   I saw an inspiring design that someone let on showcase forum where the creator showed the period that the report use. I searched how to achieve it but I don't found anything, Do any ...
Need Help
  • jpereztang's avatar
    jpereztang
    10 years ago

    Considering  the test i got this

     

     

    Filtered period:=IF(
                                 FIRSTNONBLANK(Tiempo[Month],1) = LASTNONBLANK(Tiempo[Month],1)
                                ,FIRSTNONBLANK(Tiempo[Month],1)
                                ,FIRSTNONBLANK(Tiempo[Month],1) & " ~ " & LASTNONBLANK(Tiempo[Month],1) )

     

    Tecnically this answer my question, what do you think Sean Anonymous

     

     

    UPDATE:

     

    After do some test i found that FIRSTNONBLANK(Tiempo[Month]) get the first month order alphabetically because i want to show something like 'Jan-2016' and this is not the order I want. so I had to use FIRSTDATE to get the correct dates order. Finally this is the measure that resolve the problem

     

     

    Filtered period:=IF(
                  FORMAT(FIRSTDATE(DimDate[Date]),"M")+ FORMAT(FIRSTDATE(DimDate[Date]),"yyyy") = 
                        FORMAT(LASTDATE(DimDate[Date]),"M") +  FORMAT(LASTDATE(DimDate[Date]) ,"yyyy")
                  ,FORMAT(FIRSTDATE(DimDate[Date]),"MMM") & "-" & FORMAT(FIRSTDATE(DimDate[Date]),"yyyy")
                  ,FORMAT(FIRSTDATE(DimDate[Date]),"MMM") & "-" & FORMAT(FIRSTDATE(DimDate[Date]),"yyyy")
                        & " ~ " & FORMAT(LASTDATE(DimDate[Date]),"MMM") & "-" & FORMAT(LASTDATE(DimDate[Date]) ,"yyyy")
 )

     

    I hope it will be usefull for someone

Sean's avatar
Sean
Community Champion
10 years ago

jpereztang the only thing about the VARiable solution - it may run slower than the others depending on how much data you have!

 

Not that you'll notice anything but it may be more taxing on the system - I may or may not test it in DAX Studio later :smileyhappy:

 

Anonymous's avatar
Anonymous
Not applicable
10 years ago

Sean The impact of VAR on performance is a bit hard for me to predict. Sometimes it gives you a huge performance gain if it's something that would otherwise have been re-calculated multiple times. In this case I think it may be a wash. Whether you stick it in a VAR or not each of those two dates will only be called once. So you could write the same formula as

 

Report Period = FORMAT( FIRSTDATE(DateTable[Date]), "MMM yyyy")

& " - " &

FORMAT(LASTDATE(DateTable[Date]), "MMM yyyy")

 

I'm curious to see how the two versions perform against each other, but it's so simple I'll bet there's little or no difference.

  • Sean's avatar
    Sean
    Community Champion
    10 years ago

    Anonymous okay you made me test - so on the dataset I tested the VAR and First/Last date are pretty much the same.

     

    Firstnon/Lastnon slightly edged them both out.. go figure :smileyhappy:

     

    Basically jpereztang experiment and see which works best for you!

     

    Knowing performance was about the same - VAR does give you more flexibility (all you need is a date)

     

    If you have 2 diiferent dates VAR may actually help you - gotta go test something else now... :smileyhappy: