Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Thank you in advance for any suggestions/advices.
RV
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!
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
56 | |
27 | |
24 | |
14 | |
9 |
User | Count |
---|---|
77 | |
61 | |
47 | |
17 | |
12 |