Forum Discussion
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 of you know how to do it ?
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
16 Replies
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.
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!
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.
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 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:
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)
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.
Anonymous 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
Many options, yes. The question is what to accept as a solution? lol
Thank you guys ! Sean Anonymous
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 :smileyhappy:
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.
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. :smileyvery-happy:
