topic Re: DAX measure to calculate previous month to date income based on selected week from slicer in DAX Commands and Tips

DAX measure to calculate previous month to date income based on selected week from slicer

RJ_KON — Fri, 16 Feb 2024 13:34:05 GMT

Hi Community, I would like to create one simple measure for previous month income without using "date" field.

So I have created some calculated columns in my dim calendar and below are the columns

1. Weemeasure to bring all week numbers

2. Periodmeasure to bring all period numbers

4. quartermeasure to bring all quarter numbers

5. Yearmeasure to bring all years

6. weekslicer for my slicer to select week number

I could able to get below attributes by selecting week from my week slicer.

1 . Current week, previous week and same week last year

2. Current month to date and last year month to date

3. Current quarter to date and last year quarter to date

4. Current year to date and last year to date

The only two measures I'm stuggling to get are same year previous month to date and previous quarter to date by selecting week from week slicer.

The measures that I'm trying are

Previous MTD for Current year= CALCULATE( [Actual Income], ALL('Dim Calendar'),

'Dim Calendar'[Week Measure]<=SELECTEDVALUE('Dim Calendar'[Week Measure]),

'Dim Calendar'[Yearmeasure]=SELECTEDVALUE('Dim Calendar'[Yearmeasure]),

'Dim Calendar'[Period Measure]=SELECTEDVALUE('Dim Calendar'[Period Measure])-1)

Previous QTD for Current year= CALCULATE( [Actual Income], ALL('Dim Calendar'),

'Dim Calendar'[Week Measure]<=SELECTEDVALUE('Dim Calendar'[Week Measure]),

'Dim Calendar'[Yearmeasure]=SELECTEDVALUE('Dim Calendar'[Yearmeasure]),

'Dim Calendar'[Quarter Measure]=SELECTEDVALUE('Dim Calendar'[Quarter Measure])-1)

Thank you in advance for any suggestions/advices.

Re: DAX measure to calculate previous month to date income based on selected week from slicer

Sahir_Maharaj — Sun, 18 Feb 2024 16:16:10 GMT

Hello RJ_KON,

Can you please try the following DAX:

Previous Month to Date (MTD) for Current Year

Previous MTD for Current Year = VAR SelectedWeek = SELECTEDVALUE('Dim Calendar'[Week Measure]) VAR SelectedYear = SELECTEDVALUE('Dim Calendar'[Yearmeasure]) VAR PreviousMonth = SELECTEDVALUE('Dim Calendar'[Period Measure]) - 1 RETURN CALCULATE( [Actual Income], ALL('Dim Calendar'), 'Dim Calendar'[Week Measure] <= SelectedWeek, 'Dim Calendar'[Yearmeasure] = SelectedYear, 'Dim Calendar'[Period Measure] = PreviousMonth )

Previous Quarter to Date (QTD) for Current Year

Previous QTD for Current Year = VAR SelectedWeek = SELECTEDVALUE('Dim Calendar'[Week Measure]) VAR SelectedYear = SELECTEDVALUE('Dim Calendar'[Yearmeasure]) VAR PreviousQuarter = SELECTEDVALUE('Dim Calendar'[Quarter Measure]) - 1 RETURN CALCULATE( [Actual Income], ALL('Dim Calendar'), 'Dim Calendar'[Week Measure] <= SelectedWeek, 'Dim Calendar'[Yearmeasure] = SelectedYear, 'Dim Calendar'[Quarter Measure] = PreviousQuarter )

Hope this helps!

Re: DAX measure to calculate previous month to date income based on selected week from slicer

RJ_KON — Thu, 22 Feb 2024 16:02:26 GMT

Hi Sahir, thanks for the measures. I tried and still it is giving me last period full month Income.
Today I tried week 47 in my slicer for period 11 and I want to see only three weeks income from previous period 10, but above measure giving me full month Income.

Regards
RJ_KON