How to exclude / filter last month YTD-calculation

[JohanData]

Hi there,

Could you please help me out with the following?

Imagine: you have a dataset with only YTD-data. Ths facttable has only 2 columns: date & Quantity YTD. In order to caculate the amount 'between' the months, you make the following measures:

Quantity YTD =

sum ( 'FACT'[Quantity YTD])

Quantity YTD PM =

CALCULATE ( [Quantity YTD] , DATEADD( DIM_Calender[Date] , -1 , MONTH ))

Quantity =

[Quantity YTD] - [Quantity YTD PM]

However, the problem of the measure 'Quantity YTD PM' is that this measure takes the Total YTD data of the previous month. I added the PBI-file to this question.

@https://1drv.ms/u/s!AvDjQoL_zmjllyEdBZBy97102_3Y

Please help me out.

Outcome 'Quantity' should be: 2019-01 = 0 and Total = 310

[v-zhenbw-msft]

Hi @JohanData ,

Sorry for that we have a little confused about your issue.

The Quantity YTD PM shows the 300 in 2019-01, is it the value in 2018-12?

You Quantity logic is Quantity = [Quantity YTD] - [Quantity YTD PM], what is the 0 logic in 2019-01? Do you want to show the blank in Quantity YTD PM, then the Quantity is 0?

BTW, your file is not existed. Could you upload again? Please don't contain any Confidential Information or Real data in your reply.

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[JohanData]

Hi there, I adjusted the link. Please check again 🙂

[JohanData]

The Quantity YTD PM shows the 300 in 2019-01, is it the value in 2018-12? --> Yes, it's the value of the previous month.

You Quantity logic is Quantity = [Quantity YTD] - [Quantity YTD PM], what is the 0 logic in 2019-01? Do you want to show the blank in Quantity YTD PM, then the Quantity is 0? --> it should be 0 because the year starts here. 

Thanks for asking the right questions and helping me out. 

[JohanData]

Hi there,

Your solution: https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Revenue-Reverse-YTD/m-p/373185#M111

does not work if you plot your solution in a matrix-table.

[v-zhenbw-msft]

Hi @JohanData ,

Please allow us to confirm once again whether our understanding is correct.

When we select the 2019, in 2019-01, the Quantity YTD will show 30, Quantity YTD Previous Month will show 300, and the Quantity will show 0.

If it is your requirement, we can add an IF function in your Quantity measure.

Quantity = 
IF(
    MAX(DIM_Calender[Monthnumber])=1,0,
    [Quantity YTD] - [Quantity YTD PM])

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

BTW, pbix as attached.

[JohanData · Accepted solution]

Hi there,

No, this does not meet the requirements if you roll it up to year. 

Anyhow, this is how fixed the issue myself:

# Quantity Prep =

var quantity =

CALCULATE ( [# Quantity YTD] - [# Quantity YTD PM] , DIM_Calendar[Monthnumber] <> 1 )

var selected_month = SELECTEDVALUE ( DIM_Calendar[Monthnumber])

return

SWITCH ( true,

selected_month = 1 , [# Quantity YTD] ,

ISBLANK([# Quantity YTD]) , BLANK() ,

quantity

)

# Quantity =

sumx ( VALUES ( DIM_Calendar[Year/Month] ) , [# Quantity Prep])

[Greg_Deckler]

@JohanData See if these help: 

https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Revenue-Reverse-YTD/m-p/373185#M111

Also, you may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...