Summarize Table and Filter by Date

[davehus]

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 🙂 

[Anonymous]

Hi All,

What if I want to filter the summarized table using one of the aggregated columns?

[vanessafvg]

@Anonymous can you provide an example of what you saying?

[v-jiascu-msft · Accepted solution]

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]

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

[davehus]

Thank you @v-jiascu-msft & @vanessafvg

[vanessafvg]

@davehus  don't know if this helps

https://blogs.msdn.microsoft.com/karang/2013/02/15/dax-using-filter-and-summarize-in-same-query/