Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Date measures

Hi All,   I have a table with date , quantity and customer ID. The output table needs to have  what was shipped today shipped yesterday Week to date shipment Month to date shipment , and Yea...
Need Help
  • v-alq-msft's avatar
    v-alq-msft
    5 years ago

    Hi, Anonymous 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

    Table:

     

    You may create measures as below.

    Today Qty = 
CALCULATE(
    SUM('Table'[Quantity]),
    FILTER(
        ALLEXCEPT('Table','Table'[CustomerID]),
        [Date]=TODAY()
    )
)
    Yesterday Qty = 
CALCULATE(
    SUM('Table'[Quantity]),
    FILTER(
        ALLEXCEPT('Table','Table'[CustomerID]),
        [Date]=TODAY()-1
    )
)
    WTD Qty = 
CALCULATE(
    SUM('Table'[Quantity]),
    FILTER(
        ALLEXCEPT('Table','Table'[CustomerID]),
        YEAR([Date])*100+WEEKNUM([Date])=YEAR(TODAY())*100+WEEKNUM(TODAY())&&
        [Date]<=TODAY()
    )
)
    MTD Qty = 
CALCULATE(
    SUM('Table'[Quantity]),
    FILTER(
        ALLEXCEPT('Table','Table'[CustomerID]),
        YEAR([Date])*100+MONTH([Date])=YEAR(TODAY())*100+Month(TODAY())&&
        [Date]<=TODAY()
    )
)
    YTD Qty = 
CALCULATE(
    SUM('Table'[Quantity]),
    FILTER(
        ALLEXCEPT('Table','Table'[CustomerID]),
        YEAR([Date])=YEAR(TODAY())&&
        [Date]<=TODAY()
    )
)

     

    Result:

     

    You may remove the calculated columns. They are created just for clear distinction. Today is 3/3/2021.

     

    Best Regards

    Allan

     

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

smpa01's avatar
smpa01
Community Champion
5 years ago

Anonymous  you need a DateTbl to start with in order for DAX to perform all time intelligence calculation.

 

For now, you can do the following to return the first two

_shippedToday:= CALCULATE(SUM('Table'[Column1]),FILTER('Table','Table'[date]=TODAY()))

_shippedYesterday = CALCULATE(SUM('Table'[Column1]),FILTER('Table','Table'[date]=TODAY()-1))