SAMEPERIODLASTYEAR with two filters

DAX DAX Calculations
1

Hi all,
I’m preparing a sample PBIX file but wanted to post my question in case one knows an answer.

From 100 products that we make we actively plan/forecast only 40 or so. The rest is non-planned based on last years (LY) numbers. Everything in this report is on a daily basis.
We have a full fiscal year monthly budget, that I broke down in daily numbers.
I have the daily actual produced products. All dates in the past have only actuals against budget.
For future dates, (>= today) I take the value of the 40 planned items + the LY numbers of the 60 items.

I managed to get the measure of LY figures for those that do not have a planned item number, i.e. are blank:

Non Planned Items Plan MT =
CALCULATE (
[Actual MT],
SAMEPERIODLASTYEAR ( dim_date[Dates] ),
FILTER (
dim_Item_Attributes,
dim_Item_Attributes[Planned Item Number]
= BLANK ()
)
)

However, this measure returns the LY figures for all dates. Once today has passed, this should be blank and only actual remain. For >= today, it should show plan + LY nonplanned.

I tried many different calculations, but they all return blank. Here’s one which I thought to just add another filter in it, as this worked with other stuff as well.

Non Planned Items Plan MT =
CALCULATE (
[Actual MT],
SAMEPERIODLASTYEAR ( dim_date[Dates] ),
FILTER (
dim_Item_Attributes,
dim_Item_Attributes[Planned Item Number]
= BLANK ()
),
FILTER (
dim_date,
dim_date[Dates]
>= TODAY ()
)
)

In the image the orange numbers should be blank. (Same as the Plan MT is blank < today).
image

I’ll be working on a PBIX sample file, but if you already have an idea, please let me know.

Cheers
Kat

3

I think the issue is with the model getting the data from a direct query. I thought a workaround through SELECTEDCOLUMNS would do the trick, but the DAX behaviour is different than from stored models.

I don’t have the issues in the sample file, so that’s my only explanation.

Cheers
Kat

4

@Kathi You can do a simple IF condition to check IF ( SELECTEDVALUE ( Dates[Date] ) >= TODAY(), [Non Planned Items Plan MT] )

5

Hi @Kathi , did the response provided by @AntrikshSHarma help you solve your query?

If not, kindly send the PBIX file so the other users can help you with your inquiry further.

If yes, kindly mark as solution the answer that solved your query.

6

Hi @Kathi, we’ve noticed that no response has been received from you since September 20.

We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

7

Hi @Kathi, due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please create a new thread.