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

jpereztang's avatar
jpereztang
Helper I
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

jpereztang I see, you're trimming it up for cases when there's only one month. Smart. Looks good to me. That might be a good case for the VAR method, so that you don't have to call FIRSTNONBLANK multiple times. But Sean would have to test to tell us if there's any performance gain there. It would be milliseconds if anything I'm sure. :smileytongue:

  • Sean's avatar
    Sean
    Community Champion
    10 years ago

    Anonymous :smileylol: I'm done testing for today :smileylol:

     

    I implemented the VAR approach and it works great with 2 different dates in the same table (no need for USERELATIONSHIP)