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

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: