Fetching previous month value in calculation.

[NasiraliKarwar]

Hello, 

I am trying to fetch a value from a previous month in my further month's calculations:

First logic: 

test overall due = CALCULATE(COUNT('Power BI'[Due Cases]), ALL('Power BI'))

Second logic:

Rolled Up Renewed Cases =

IF(

     MONTH(MAX('Date Table'[Date])) = 1, BLANK(),

        CALCULATE(

            COUNT('Power BI'[Renew Cases]),

            FILTER(

                ALL('Date Table'),

                MONTH('Date Table'[Date]) = MONTH(MAX('Power BI'[Update date]))

            )

        )

)

Third logic (where I need help): 

test measure =

 IF(

     MONTH(MAX('Date Table'[Date])) = 1,

            [test overall due],

 (CALCULATE([test overall due], 'Date Table'[Date]) - [Rolled Up Renewed Cases]) - CALCULATE([Rolled Up Renewed Cases], PREVIOUSMONTH('Date Table'[Date]))

 )

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]].

this is the current outcome:

What I am expecting:

Please assist!

[Ashish_Mathur]

Hi,

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

[NasiraliKarwar]

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.

 

Outcome I am seeing:

Expected outcome: 

 

[Ashish_Mathur]

Sorry I am not clear.  Someone else may help you. 

[Daniel29195]

@NasiraliKarwar 

output :  ( COLUMN : measure  )

use this measure : 

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] <= MAX(tbl[monthnb]),
    REMOVEFILTERS(tbl[month])
)


return first_value  - v

NB: you need to create a monthnb and sort monthname column by monthnb .

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution !✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

[NasiraliKarwar]

Thanks for response @Daniel29195 !   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.