Forum Discussion

davehus's avatar
davehus
Memorable Member
8 years ago
Solved

Summarize Table and Filter by Date

Hi, 

 

I have a summarized table but I want to filter to the current year. How to I amend the code below to make that happen? 

Order Profile = 
SUMMARIZE (
    'Sales Table',
    'Sales Table'[Order_Num_Key],
    Customer[Sector],
    "Total Value", SUM ( 'Sales Table'[Net Invoice Value] ),
    "Order Count", DISTINCTCOUNT('Sales Table'[Order_Num_Key])
)

 Thanks :) 

Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    Hi davehus,

     

    Try something like below please. "DimDate" is a date table.

    Order Profile =
CALCULATETABLE (
    SUMMARIZE (
        'Sales Table',
        'Sales Table'[Order_Num_Key],
        Customer[Sector],
        "Total Value", SUM ( 'Sales Table'[Net Invoice Value] ),
        "Order Count", DISTINCTCOUNT ( 'Sales Table'[Order_Num_Key] )
    ),
    YEAR ( DimDate[Datekey] ) = YEAR ( TODAY () )
)

    Best Regards,

    Dale

6 Replies