Calculate formula

[Anonymous]

I have to calculate running total based on created date. I have one table where all the transaction is stored and other table has created date along with quarter. I used concatenate to create a key (created date + created quarter) in transaction as well as date table. Using this key I am getting day of quarter from date table. Now I am calculating running total for Previous quarter and passing dayofQtr as filter but getting wired result.

PQ_Rev =

CALCULATE ([QTD],

    FILTER ( ALL(Revenue_Table), Revenue_Table[Fiscal Quarter] = "FY18-Q3" && Revenue_Table[DayofQtr] <= [DayOfPQ])

) 

This formula return incorrect number . DayofPQ is of same data type as “Revenue_Table[DayofQtr]”

PQ_Rev_Test =

CALCULATE ([QTD],

    FILTER ( ALL(Revenue_Table), Revenue_Table[Fiscal Quarter] = "FY18-Q3" && Revenue_Table[DayofQtr] <= 24)

)

This formula return correct result. I just pass actual day (24) here.

DayOfPQ is derived from created date (same date last quarter - Start of the last quarter )

Can anyone help how to get correct result using dynamic filter (DayOfPQ)

[Zubair_Muhammad · Accepted solution]

@Anonymous

May be you just need to store the MEASURE value in a variable first

Try this

PQ_Rev_Test =
VAR myvalue = [DayOfPQ]
RETURN
    CALCULATE (
        [QTD],
        FILTER (
            ALL ( Revenue_Table ),
            Revenue_Table[Fiscal Quarter] = "FY18-Q3"
                && Revenue_Table[DayofQtr] <= myvalue
        )
    )

[Anonymous]

@Zubair_Muhammad Thanks