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'[Ord...
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

v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Pleae can you explain the solution ? why we  can't use dimate filter within the SUMMARIZE funciton.