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

Anonymous My initial reaction was same as yours so I tested it and it seems it still sorts alphabetically!

 

When the Month is a Number (even though column Year-Mo is Text) its okay like here...

 

Report FIRSTNON/LASTNON 2 = 
IF (
    FIRSTNONBLANK ( 'Calendar'[Year-Mo], 1 ) = LASTNONBLANK ( 'Calendar'[Year-Mo], 1 ),
    FIRSTNONBLANK ( 'Calendar'[Year-Mo], 1 ),
    FIRSTNONBLANK ( 'Calendar'[Year-Mo], 1 ) & " ~ " & LASTNONBLANK ( 'Calendar'[Year-Mo], 1 )
)

But with Month Name doesn't work...

NOTE: Month Name Column is Sorted by Month Order Column - so in visualizations it sorts correctly - that is not alphabetically!

 

Report FIRSTNON/LASTNON 3 = 
IF (
    FIRSTNONBLANK ( 'Calendar'[Month-Year], 1 ) = LASTNONBLANK ( 'Calendar'[Month-Year], 1 ),
    FIRSTNONBLANK ( 'Calendar'[Month-Year], 1 ),
    FIRSTNONBLANK ( 'Calendar'[Month-Year], 1 ) & " ~ " & LASTNONBLANK ( 'Calendar'[Month-Year], 1 )
)

See picture

Anonymous's avatar
Anonymous
Not applicable
10 years ago

Sean huh. Well I guess we're right back to reformatting the results of a FIRSTDATE function then.