Get period context

[jpereztang]

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 of you know how to do it ?

[jpereztang · Accepted solution]

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 @KHorseman

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

[KHorseman]

@jpereztang that's easy to fix. You need a numeric column on your date table that matches the order that the months should follow. Add this column:

Month Order = INT(CONCATENATE(YEAR(DateTable[Date]), CONCATENATE(IF(MONTH(DateTable[Date]) < 10, "0", ""), MONTH(DateTable[Date]))))

Then select your Month of Year column and use the Sort By Column button to tell it to use Month Order to sort Month of Year. The months will now be sorted in calendar order instead of alphabetic.

[Sean]

@KHorseman 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

[KHorseman]

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

[KHorseman]

@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.

[Sean]

@KHorseman I'm done testing for today

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

[jpereztang]

Many options, yes. The question is what to accept as a solution? lol

Thank you guys ! @Sean @KHorseman

[Sean]

@jpereztang the only thing about the VARiable solution - it may run slower than the others depending on how much data you have!

Not that you'll notice anything but it may be more taxing on the system - I may or may not test it in DAX Studio later

[KHorseman]

@Sean The impact of VAR on performance is a bit hard for me to predict. Sometimes it gives you a huge performance gain if it's something that would otherwise have been re-calculated multiple times. In this case I think it may be a wash. Whether you stick it in a VAR or not each of those two dates will only be called once. So you could write the same formula as

Report Period = FORMAT( FIRSTDATE(DateTable[Date]), "MMM yyyy")

& " - " &

FORMAT(LASTDATE(DateTable[Date]), "MMM yyyy")

I'm curious to see how the two versions perform against each other, but it's so simple I'll bet there's little or no difference.

[Sean]

@KHorseman okay you made me test - so on the dataset I tested the VAR and First/Last date are pretty much the same.

Firstnon/Lastnon slightly edged them both out.. go figure

Basically @jpereztang experiment and see which works best for you!

Knowing performance was about the same - VAR does give you more flexibility (all you need is a date)

If you have 2 diiferent dates VAR may actually help you - gotta go test something else now...

[KHorseman]

@jpereztang Technically I think my last post was the most flexible and broadly applicable solution, but you could probably just roll the dice and pick any one.

[KHorseman]

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]

@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!

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

[KHorseman]

@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]

Yes many options indeed!

[Sean]

@jpereztang Just one more thing you may find useful and ties in perfectly with this thread

Check out @KHorseman's solution here

http://community.powerbi.com/t5/Desktop/Implement-different-periods-in-reports/m-p/32713#M11452