Forum Discussion
DAX Help with Date Question
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
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
2 Replies
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.
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
