Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
CurrentMonthLastYearCount = VAR SelectedMonth = MAX(Query1[SetupDate]) VAR LastYearSameMonthStart = EOMONTH(SelectedMonth, -12) + 1 VAR LastYearSameMonthEnd = EOMONTH(SelectedMonth, -12) RETURN CALCULATE( SUM(Query1[NewCount]), Query1[SetupDate] >= LastYearSameMonthStart && Query1[SetupDate] <= LastYearSameMonthEnd )
I am trying to write a query which shows current month last year in my matrix based on slicer date selection but this query wont return any reuslts and would show up as blank
Solved! Go to Solution.
@homelander123 If I understand what you are saying, you could do something like this and put it in a Card visual:
Measure =
VAR SelectedMonth = MAX(Query1[SetupDate])
VAR LastYearSameMonthEnd = EOMONTH(SelectedMonth, -12)
VAR Result = FORMAT( LastYearSameMonthEnd, "mmm yyyy" )
RETURN
Result
@homelander123 I think you want this:
CurrentMonthLastYearCount =
VAR SelectedMonth = MAX(Query1[SetupDate])
VAR LastYearSameMonthStart = EOMONTH(SelectedMonth, -13) + 1
VAR LastYearSameMonthEnd = EOMONTH(SelectedMonth, -12)
VAR Result = CALCULATE( SUM(Query1[NewCount]), Query1[SetupDate] >= LastYearSameMonthStart && Query1[SetupDate] <= LastYearSameMonthEnd )
RETURN
Result
This works. Thank you so much. Can you please tell me how can i make the name dynamic to the slicer. E.g, if i have selected feb 2025 it should show feb 2024
@homelander123 If I understand what you are saying, you could do something like this and put it in a Card visual:
Measure =
VAR SelectedMonth = MAX(Query1[SetupDate])
VAR LastYearSameMonthEnd = EOMONTH(SelectedMonth, -12)
VAR Result = FORMAT( LastYearSameMonthEnd, "mmm yyyy" )
RETURN
Result
User | Count |
---|---|
25 | |
11 | |
7 | |
6 | |
6 |
User | Count |
---|---|
30 | |
13 | |
11 | |
9 | |
6 |