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 

  1. what was shipped today
  2. shipped yesterday
  3. Week to date shipment
  4. Month to date shipment , and
  5. Year to date shipment

I used PREVIUOSDAY for yesterday, but its giving incorrect result. : 

Yest = CALCULATE(SUM('FRS Shipment measures'[Weight In Tons]),PREVIOUSDAY('FRS Shipped Date'[ShippedDate]))

How should I create seperate query for each of the five above. Or should it be a calculated colum. Please help me out, for this urgent Deliv.

 

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.

3 Replies

  • 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))
  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thanks a lot for help!