Every month 1st week wednesday data should reflect

[Saipreethi]

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.

[Jihwan_Kim]

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")
    )

[amitchandak · Accepted solution]

@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)) ) )