Sales vs Budget YTD but skip Budget for Months that have not happened yet

[mblondinde]

Hello,

I'm pretty new to Powerpivot. I've had so much fun going through Rob Collie's book but now it's time to do real stuff and ... seems that I can't

It seems pretty simple to me but I can't get it to work. Here's my layout.

- 1 source for my products
- 1 source for my dates (1 entry per month)
- 1 source for my sales (granularity month)
- 1 source for my budget (granularity month)

Everything is related properly, I have Item# and Period in both my "fact" sources. I'm trying to create a YTD measure for my Sales and also Budget. But the thing is that now we're in March, I want my YTD Budget figure to only sum up until March, not all data for the year I have selected in my slicer. But by using the DATESYTD function, it adds up the complete year data.

I would like to setup a measure that automatically performs the YTD in the budget based on the latest period that exists in my Sales source. Am I asking too much?

Thanks for any help.

Regards,
Martin

 

[Mavericks334]

Create a dates table and year period table and try using the below

measure =CALCULATE([Total_Sales],DATESBETWEEN(Dates[Dates],DATEADD(LASTDATE(Year_Period[Next_Month_Start_Date]),MAX(Year_Period[Calendar_Period])*-1,MONTH),LASTDATE(Year_Period[Month_End_Date])))