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: