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 I guess it would be something like this...

 

Report Period = FIRSTDATE ( 'Calendar'[Date] ) & " - " & LASTDATE ( 'Calendar'[Date] )

Seems to work in my case...

 

 

 

EDIT: Which formula you end up using depends on what DATA TYPE column you use!

 

Anonymous's would work on dates formatted as TEXT as well so go with it!

Anonymous's avatar
Anonymous
Not applicable
10 years ago

Sean or you could just get goofy and do something like

 

Report Period =

VAR fdate = FIRSTDATE(DateTable[Date]) 

VAR ldate = LASTDATE(DateTable[Date])

RETURN

FORMAT(fdate, "MMM yyyy") & " - " & FORMAT(ldate, "MMM yyyy")

 

 

...I say goofy, but that does negate the need for the Month of Year column. All you need is the date. This sort of auto-generates the results of the Month of Year column but just for those two dates. Anyway jpereztang now you have lots of options.

  • Sean's avatar
    Sean
    Community Champion
    10 years ago

    :smileyvery-happy: Yes many options indeed! :smileyvery-happy: