topic Re: Sameperiodlastyear() and month filter in DAX Commands and Tips

Sameperiodlastyear() and month filter

_-Christian-_ — Tue, 23 Apr 2024 09:43:02 GMT

Hello,

I want to make a forecast with values from the last year, if the sixth month of the forecast is startet.

I try the following Dax formula:

var Cost = Calculate(

sum(Table[Cost]),

USERELATIONSHIP('Calendar'[Date],'Table'[Close Date]),

SAMEPERIODLASTYEAR('Calendar'[Date]),
Filter('Calendar'[Date], 'Calendar'[Date]>TODAY()+366/2)

)

I recieve the following output.

It look likes that the filterfunction doesn't work here. The first entry should be in September.

Can someone support me here?

Re: Sameperiodlastyear() and month filter

lbendlin — Tue, 23 Apr 2024 17:02:40 GMT

May want to use DATEADD or EDATE instead,

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: Sameperiodlastyear() and month filter

_-Christian-_ — Wed, 24 Apr 2024 10:43:13 GMT

Thanks for your reply!

I figured out a solution for the problem. Instead of using today() in the filter context, I create a new variable. I cannot explain why it works now, but it works.

var today_ = today() -366/2

var Cost = Calculate(

sum(Table[Cost]),

USERELATIONSHIP('Calendar'[Date],'Table'[Close Date]),

SAMEPERIODLASTYEAR('Calendar'[Date]),
Filter('Calendar'[Date], 'Calendar'[Date]> today_)

)

return
Cost