Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Sum previous month without filter

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

 

Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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?

       

  • v-xiaotang's avatar
    v-xiaotang
    Icon for Community Support rankCommunity Support
    4 years ago

    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.