Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Change Measure from Sum to Count Cumulative hours...

Hi Experts

 

How would you amend the following to get a cumulative count as opposed to sum....

Cumulative Hourly =
VAR currDate =
MAX ( FACTSalesOrderTable[created_at] )
RETURN
IF (
TIMEVALUE ( currDate ) <> TIME(0,0,0),
CALCULATE (
SUM ( FACTSalesOrderTable[Created_Hour] ),
FILTER (
ALLSELECTED ( FACTSalesOrderTable ),
AND (
FACTSalesOrderTable[created_at] >= DATEVALUE ( currDate ),
FACTSalesOrderTable[created_at] < currDate
)
)
),
CALCULATE (
SUM ( FACTSalesOrderTable[Created_Hour] ) + 0,
FILTER (
ALLSELECTED ( FACTSalesOrderTable ),
DATEVALUE ( FACTSalesOrderTable[created_at] )
= DATEVALUE ( currDate ) - 1
)
)
)
Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    Anonymous , You can try like this ?

     

    VAR currDate =
    MAX ( FACTSalesOrderTable[created_at] )
    RETURN
    IF (
    TIMEVALUE ( currDate ) <> TIME(0,0,0),
    CALCULATE (
    count ( FACTSalesOrderTable[Created_Hour] ),
    FILTER (
    ALLSELECTED ( FACTSalesOrderTable ),
    AND (
    FACTSalesOrderTable[created_at] >= DATEVALUE ( currDate ),
    FACTSalesOrderTable[created_at] < currDate
    )
    )
    ),
    CALCULATE (
    count ( FACTSalesOrderTable[Created_Hour] ) + 0,
    FILTER (
    ALLSELECTED ( FACTSalesOrderTable ),
    DATEVALUE ( FACTSalesOrderTable[created_at] )
    = DATEVALUE ( currDate ) - 1
    )
    )
    )

     

     

     

    I think this option should have work for normal cumulative

     

    CALCULATE (
    count ( FACTSalesOrderTable[Created_Hour] ) + 0,
    FILTER (
    ALLSELECTED ( FACTSalesOrderTable ),
    FACTSalesOrderTable[created_at] < =max ( FACTSalesOrderTable[created_at] )
    )
    )

4 Replies

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

    Anonymous , You can try like this ?

     

    VAR currDate =
    MAX ( FACTSalesOrderTable[created_at] )
    RETURN
    IF (
    TIMEVALUE ( currDate ) <> TIME(0,0,0),
    CALCULATE (
    count ( FACTSalesOrderTable[Created_Hour] ),
    FILTER (
    ALLSELECTED ( FACTSalesOrderTable ),
    AND (
    FACTSalesOrderTable[created_at] >= DATEVALUE ( currDate ),
    FACTSalesOrderTable[created_at] < currDate
    )
    )
    ),
    CALCULATE (
    count ( FACTSalesOrderTable[Created_Hour] ) + 0,
    FILTER (
    ALLSELECTED ( FACTSalesOrderTable ),
    DATEVALUE ( FACTSalesOrderTable[created_at] )
    = DATEVALUE ( currDate ) - 1
    )
    )
    )

     

     

     

    I think this option should have work for normal cumulative

     

    CALCULATE (
    count ( FACTSalesOrderTable[Created_Hour] ) + 0,
    FILTER (
    ALLSELECTED ( FACTSalesOrderTable ),
    FACTSalesOrderTable[created_at] < =max ( FACTSalesOrderTable[created_at] )
    )
    )

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous 

    Do you want to calculate cumulative sum of hours Per category(GBP,AUD...)?

    Here I will give you some advice.

     

    Cumulative Hourly =
VAR currDate =
MAX ( FACTSalesOrderTable[created_at] )
RETURN
IF (
TIMEVALUE ( currDate ) <> TIME(0,0,0),
CALCULATE (
SUM ( FACTSalesOrderTable[Created_Hour] ),
FILTER (
ALLSELECTED ( FACTSalesOrderTable ),
AND (
FACTSalesOrderTable[created_at] >= DATEVALUE ( currDate ),
FACTSalesOrderTable[created_at] < currDate
)&&FACTSalesOrderTable[Category] = Max(FACTSalesOrderTable[Category])
)
),
CALCULATE (
SUM ( FACTSalesOrderTable[Created_Hour] ) + 0,
FILTER (
ALLSELECTED ( FACTSalesOrderTable ),
DATEVALUE ( FACTSalesOrderTable[created_at] )
= DATEVALUE ( currDate ) - 1
)
)
)

     

    Please add a filter FACTSalesOrderTable[Category] = Max(FACTSalesOrderTable[Category]). Category should be the column in matrix column which contains (GBP,AUD...).

    It seems that you have deleted the sample in your link. 

    Could you share a sample with me from your Onedrive for Business again? 

     

    Best Regards,

    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.