Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Calculate formula

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)

 

 

 

Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    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
        )
    )

2 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Icon for Community Champion rankCommunity Champion
    8 years ago

    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
        )
    )