topic Re: Every month 1st week wednesday data should reflect in DAX Commands and Tips

Every month 1st week wednesday data should reflect

Saipreethi — Thu, 13 Oct 2022 07:51:44 GMT

Hi everyone,

1st week (wednesday) of Every month data should reflect last months data. For eg: First week is October 5th( wednesday) and the data should reflect last month september data. Till October 4th it should show August data only. The same should be applied for entire year. Can anyone help me with the DAX.

Re: Every month 1st week wednesday data should reflect

amitchandak — Thu, 13 Oct 2022 08:36:37 GMT

Saipreethi ,

Assuming this based on today and you need full month data

var _mo = eomonth(today(), -1) +1

var _wed = _mo - Weekday(_mo,2) +3

var _final = if(_web<_mo, _wed +7, _wed)

return

if(day(_wed) <5, calculate(Sum(Table[Value]), filter(Table, Eomonth(Table[Date],0) =eomonth(today(), -2)) ),

calculate(Sum(Table[Value]), filter(Table, Eomonth(Table[Date],0) =eomonth(today(), -1)) ) )

Re: Every month 1st week wednesday data should reflect

Jihwan_Kim — Thu, 13 Oct 2022 09:07:46 GMT

Hi,

I am not sure if I understood your question correctly, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

In order to create a calculated column that suits your business requirement is to author a calculated column like below inside the calendar table.

New Month-Year CC = VAR _currentcalendarmonthyear = 'Calendar'[End of Month] VAR _startingwednesday = MINX ( FILTER ( 'Calendar', 'Calendar'[End of Month] = _currentcalendarmonthyear && 'Calendar'[Day Name] = "Wednesday" ), 'Calendar'[Date] ) RETURN IF ( 'Calendar'[Date] >= _startingwednesday, 'Calendar'[Month-Year], FORMAT('Calendar'[Start of Week], "mmm-yyyy") )