Sum previous month without filter

[Anonymous]

Hello,

Currently I am doing a sum of my workforce on the current period. To do this I use a column calculated in my calendar that allows me to put a flag on the current month. I tell it to put 1 on the month corresponding to the last month of my second table. The last month is always the current month.

The calculated column in my calendar :
Flag = if('Calendar'[Year Month]=MAX('ED Usage'[Year Month]),1,0)

My calculation measure based on this filtering :
Sum of current month's staff ED PS = CALCULATE(SUM('ED Usage'[Table.Available Staff]),Calendar[Flag]=1)

So far everything works. But now I want to calculate the sum of the number of employees of the previous month but when I try to do for example :

Sum headcount month-1 ED PS = CALCULATE('Table of measures'[Sum headcount current month ED PS],REMOVEFILTERS('Initial budget'[Flag]),DATESMTD(dateadd('ED Utilisation'[Period],-1,MONTH))

or

Sum months-1 ED PS = CALCULATE('Table of measures'[Sum current months ED PS],REMOVEFILTERS('Initial budget'[Flag]), PREVIOUSMONTH('ED Usage'[Period]))

nothing works because the flag of the original measure must take precedence over the rest.

Do you have any idea how to sum up the previous month?

Thanks a lot in advance

[v-xiaotang]

Hi @Anonymous 

Could you provide

(1) a sample file, you can replace raw data with bogus data to protect your privacy,

(2) or provide some samp data involved in the measure

(3) give your expected result based on the sample you provide

Thanks.

Best Regards,

Community Support Team _Tang

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

[amitchandak]

@Anonymous , if you select date using that flag then you can use time intelligence

last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))

else

new measure =

var _1 = MAXX(all('ED Usage), 'ED Usage'[Year Month]) //or use allselected

return

CALCULATE(SUM('ED Usage'[Table.Available Staff]),Filter(all(Calendar), Eomonth(Calendar[Date],0) =eomonth(_1,-1)))

or

new measure =

var _1 = MAXX(allselected('ED Usage), 'ED Usage'[Year Month]) //or use allselected

return

CALCULATE(SUM('ED Usage'[Table.Available Staff]),Filter((Calendar), Eomonth(Calendar[Date],0) =eomonth(_1,-1)))

[Anonymous]

Thank you for the answer, but I must specify that I also have a budget table in my template. And so my calendar table does not stop at the current month but at the farthest month that exists in my data model. In my current report, the current month is October because it is the last month of the effective table. But my budget table covers all months of the year. That's why I have set a flag. Won't this cause problems with your measurements?