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(day n) minus outlet_visit_MTD(day n-1) by salesman) Here is example of the data below. I got the data from the 1st 3 columns and wish to create the 4th column.

 

Thank you in advanced!

 

3 Replies

  • 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:

  • Mahesh0016's avatar
    Mahesh0016
    Icon for Super User rankSuper User
    3 years ago

    iteety Please try this
    calculate(TOTALMTD(SUM(outlet_visit_MTD(day n)),outlet_visit_MTD[yyyyMMdd]),ALLEXCEPT(outlet_visit_MTD,outlet_visit_MTD[Saleskey]))

    iteety if this helps you please mark as a solutions.

  • ThxAlot's avatar
    ThxAlot
    Icon for Super User rankSuper User
    3 years ago

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

    Daily.pbix