topic Time Intelligence using Measure which returns a Date in DAX Commands and Tips

Time Intelligence using Measure which returns a Date

FelipeScheihing — Wed, 14 Dec 2022 21:16:20 GMT

Dear all,

I created a measure that returns a date (Waiting Period Ends); I need to generate another measure that returns the start of the following month respecting Waiting Period Ends.

Waiting Period Ends =

if([Hours Month (-4)]>129.999,[Comparison_MaxDate_StartOftMonth(-4)]+59,

if([Hours Month (-3)]>129.999,[Comparison_MaxDate_StartOftMonth(-3)]+59,

if([Hours Month (-2)]>129.999,[Comparison_MaxDate_StartOftMonth(-2)]+59,

if([Hours Month (-1)]>129.999,[Comparison_MaxDate_StartOftMonth(-1)]+59,

if([Hours Current Month]>129.999,[Comparison_MaxDate_StartOfCurrentMonth]+59

)))))

I tried to use time intelligence DAX expressions like STARTOFMONTH, and DATEADD, but i failed

For example,

if Waiting Period Ends returns 12/14/2022

the new measure should return: 1/1/2023 (first day of the next month)

Thank you, I appreciate your time.

Re: Time Intelligence using Measure which returns a Date

Greg_Deckler — Wed, 14 Dec 2022 23:00:09 GMT

FelipeScheihing Try:

Measure = VAR __EOM = EOMONTH([Waiting Period Ends],1) VAR __Result = DATE(YEAR(__EOM), MONTH(__EOM), 1) RETURN __Result

Also, time intelligence functions are evil. You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008

Re: Time Intelligence using Measure which returns a Date

FelipeScheihing — Thu, 15 Dec 2022 00:53:40 GMT

@Greg_Deckler, thank you so much! it worked perfectly! I will check the material you shared.