DAX Help with Date Question

[Anonymous]

Hello All,

I am trying to write a DAX code in Power BI but i keep getting an error. Could you help me with this.

Table 1.

Sales Date , Sales ID, Product category are columns

Table 2

Calendar table. (created by calendarauto() ). I have divided rows in this table into week start date. (For e.g. June 6th 2021 - June 13th 2021) = June 6th Start Date. 

What would be the DAX code to count the number of Sales during the week that started on June 6th, within a certain Product category. 

Thank you All

[Anonymous · Accepted solution]

Hi @Anonymous ，

Assume that the field 'Table 1'[Product category] be apply on the slicer, you can create a measure as below:

Measure =
CALCULATE (
    COUNT ( 'Table 1'[Sales ID] ),
    FILTER (
        'Table 1',
        'Table 1'[Product category] = SELECTEDVALUE ( 'Table 1'[Product category] )
            && WEEKNUM ( 'Table 1'[Sales Date] ) = WEEKNUM ( DATE ( 2021, 6, 6 ) )
    )
)

If the above one is not working for your scenario, please provide some sample data in Table 1 &Table 2(exclude sensitive data) and your expected result with specific examples and calculation logic. Thank you.

Best Regards

[Ashish_Mathur]

Hi,

To your slicer, drag Week start date from the Calendar Table and select June 6, 2021 in that slicer.  Write these measures

Total = sum(data[sales])

Weekly total = calculate([total],datesbetween(calendar[date],min(calendar[date]),min(calendar[date])+6))

Hope this helps.