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

I don't know how they did it in that particular example, but I can think of a few ways. The easiest assumes that the period is coming from a fairly standard date table, where there is a column that I'm going to call Month of Year, which contains a concatenation of the year with the short month name for each date. If that were the case you could set a date filter for the entire report, either by setting hard dates or by using a DateDiff or MonthDiff column (another date table column counting the number of days/months/whatever between now and then, derived by simple subtraction, so you could set a filter for MonthDiff >= -4 and <= 0 for the last five months for instance). Then you could create a measure:

 

Report Period = FIRSTNONBLANK(DateTable[Month of Year], 1) & " - " & LASTNONBLANK(DateTable[Month of Year], 1)

 

Put that in a card at the top of the page and you'll have your reporting period. If the period is set by slicer this will automatically adjust whenever you change the slicer selection.

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: