summary table using dax

[Anonymous]

Hi experts,

I want to create a summary table like below, need to count customers without sale also.

My model is having customer master , calendar table and sales table

Customer ID	Financial Year	Month	Max(Sale Date)
1	2022	Aug	25-08-2022
1	2022	Sep	30-09-2022
1	2022	Oct	22-10-2022

Please help

[mangaus1111 · Accepted solution]

Hi @Anonymous ,

see the calculated table in the pbi file here below:

https://1drv.ms/u/s!Aj45jbu0mDVJiHEv6z2y8SH0EZsE?e=w7Pgir

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

[mangaus1111]

Hi @Anonymous ,

try this:

ADDCOLUMNS(

   SUMMARIZE('Sales',

                        'Customer'[Customer ID],

                        'Calendar'[Financial Year],

                        'Calendar'[Month]

                   ),

      "Max (Sale Date)",MAX('Sales'[Order Date])

)

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

[Anonymous]

Hi @mangaus1111 ,
I tried below queries, in first case it will always return max sale date, in second case, the query will omit customers without sales.

 ADDCOLUMNS(

    SUMMARIZE('SALES_DASHBOARD',

                         'ACX_CUSTMASTER_BI'[CUSTOMER_CODE],

                         'Calendar'[FISCAL YEAR],

                         'Calendar'[MONTH NAME]

                    ),

       "Max (Sale Date)",MAX('SALES_DASHBOARD'[INVOICE_DATE])

)

SUMMARIZE('SALES_DASHBOARD',

                        'ACX_CUSTMASTER_BI'[CUSTOMER_CODE],

                        'Calendar'[FISCAL YEAR],

                        'Calendar'[MONTH NAME],

                        "Max (Sale Date)",MAX('SALES_DASHBOARD'[INVOICE_DATE])

                   )