Forum Discussion
Get period context
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
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
Sean huh. Well I guess we're right back to reformatting the results of a FIRSTDATE function then.
