Fetching previous month value in calculation.

Question

Hello,&nbsp;I am trying to fetch a value from a previous month in my further month's calculations:&nbsp;First logic:&nbsp;test overall due = CALCULATE(COUNT('Power BI'[Due Cases]), ALL('Power BI'))&nbsp;Second logic:Rolled Up Renewed Cases =IF(&nbsp; &nbsp; &nbsp;MONTH(MAX('Date Table'[Date])) = 1, BLANK(),&nbsp; &nbsp; &nbsp; &nbsp; CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; COUNT('Power BI'[Renew Cases]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ALL('Date Table'),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; MONTH('Date Table'[Date]) = MONTH(MAX('Power BI'[Update date]))&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; ))&nbsp;Third logic (where I need help):&nbsp;test measure =&nbsp;IF(&nbsp; &nbsp; &nbsp;MONTH(MAX('Date Table'[Date])) = 1, &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [test overall due],&nbsp;&nbsp;(CALCULATE([test overall due], 'Date Table'[Date]) - [Rolled Up Renewed Cases]) - CALCULATE([Rolled Up Renewed Cases], PREVIOUSMONTH('Date Table'[Date]))&nbsp;)&nbsp;basically, I want to display the [test overall due] value only for Jan. For rest of the months, it should display previous value of third measure ([test measure]) - Current value of [[Rolled Up Renewed Cases]].&nbsp;this is the current outcome:&nbsp;What I am expecting:&nbsp;&nbsp;Please assist!

ashish_mathur · Answer

Hi,
It will be nice if you can share some data to work, explain the simple logic of what you want done.&nbsp; Show also the expected result.&nbsp; With this much information, I am sure i can simplify your measures.

nasiralikarwar · Answer

Thanks for response&nbsp;Daniel29195&nbsp;!&nbsp; &nbsp;This seems like it should work, but I am not sure what is going wrong. I am still getting wrong values in the calculations you proposed. Let me verify again and revert back with an explanation.

daniel29195 · Answer

NasiraliKarwar&nbsp;output :&nbsp; ( COLUMN : measure&nbsp; )&nbsp;&nbsp;&nbsp;use this measure :&nbsp;&nbsp;&nbsp;&nbsp;measure =

var first_value = 
CALCULATE(
    [test_measure],
    INDEX(
        1,
    ALLSELECTED(tbl[month],tbl[monthnb]),
    ORDERBY(tbl[monthnb] , asc)
    )
)

var v = 
CALCULATE(
    SUM([value]),
    tbl[monthnb] &lt;= MAX(tbl[monthnb]),
    REMOVEFILTERS(tbl[month])
)

return first_value  - v&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;NB: you need to create a monthnb and sort monthname column by monthnb .&nbsp;&nbsp;&nbsp;&nbsp;If my answer helped sort things out for you,&nbsp;i would appreciate a&nbsp;thumbs up 👍&nbsp;and mark it as the solution&nbsp;!✅It makes a difference and might help someone else too. Thanks for spreading the good vibes!&nbsp;🤠&nbsp;&nbsp;

nasiralikarwar · Answer

Hello Ashish, What I am trying to achieve is this:

Calculation 1 - Overall Due Cases: If the due date is less than today's date, distinct count the Items. Dislay total due cases in January. For Rest months, previous value of Calculation 1 - Calculation 2

Calculation 2 - Overall Renewed Cases: If the Renew date is NOT BLANK, distinct count the Items. Dislay blank in January and values in rest months.

The dashboard has two tables: One coming from excel and another is a date table.