cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
chchIntech Frequent Visitor
Frequent Visitor

Re: How do I create a Month to Date and Budget total by calendar date?

Hi Lydia @v-yuezhe-msft,

 

Thanks for the PIBX file, the budget worked with the expected result. However, I did notice later with the MTD & YTD that the expected result is not correct and MTD gives a blank value.

 

For MTD I am using:  MTD = CALCULATE(SUM(bisnif[Sales Value]),DATESMTD('Master Calendar'[Date].[Date]))

(throws up a blank value)

 

YTD = CALCULATE(SUM(bisnif[Sales Value]),DATESYTD('Master Calendar'[Date],"01-31"))

(expected result is incorrect)

 

Below is the relationship between the two tables (Sales file & Master Calendar).

calendar relationship.JPG

 

Please suggest.

 

 

Thank you,

chchIntech

Moderator v-yuezhe-msft
Moderator

Re: How do I create a Month to Date and Budget total by calendar date?

Hi @chchIntech,

Could you please send me your PBIX file via Private message? You can upload PBIX file to OneDrive and send me shared link of the PBIX file.

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
drodrigues Visitor
Visitor

Re: How do I create a Month to Date and Budget total by calendar date?

Hi Lydia @v-yuezhe-msft,

 

Private messaged you the PBIX file shared link.

 

Thanks,

chchIntech

Moderator v-yuezhe-msft
Moderator

Re: How do I create a Month to Date and Budget total by calendar date?

Hi @chchIntech @drodrigues,

Please use the following DAX formulas to create MTD and YTD.

Monthtodate = CALCULATE(SUM(bisnif[Sales Value]),DATESMTD('Master Calendar'[Date]))
YTDTEST = CALCULATE(SUM(bisnif[Sales Value]),DATESYTD('Master Calendar'[Date],"01-31"))
1.PNG

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.