Forum Discussion

iteety's avatar
iteety
Regular Visitor
3 years ago
Solved

How to create daily data from MTD data?

Dear All,   I got a salesman outlet visit data as MTD which is accumulated daily. How could I create new column or use DAX formula for salesman outlet daily visit? (calculation is outlet_visit_MTD(...
  • ThxAlot's avatar
    ThxAlot
    3 years ago

    Pay attention to the situation that there are gaps between dates.

    Daily.pbix

     

FreemanZ's avatar
FreemanZ
Icon for Super User rankSuper User
3 years ago

hi iteety ,

you may try to add a calculated column like:

Daily2 = 
VAR _yyyymmdd = [yyyymmdd] 
VAR _mtdpre = 
    CALCULATE(
        MAX(data[MTD]),
        ALLEXCEPT(data, data[SalesKey]),
        data[yyyymmdd]< _yyyymmdd
    )
RETURN [MTD]-_mtdpre

or 

Daily3 = 
VAR _yyyymmdd = [yyyymmdd] 
VAR _saleskey = [saleskey]
VAR _mtdpre =
MAXX(
    FILTER(
        data,
        data[saleskey]=_saleskey
            &&data[yyyymmdd]<_yyyymmdd
    ),
    data[MTD]
)
RETURN 
    [MTD] - _mtdpre

 

they worked like: