Select first item from previous group

DAX DAX Calculations
1

Hello all PowerBI geniuses, I am still studying the courses and sadly got another question.
I already have a solution to my problem, I was just wondering if there is a smarter way to do it.

I want to calculate cumulative total revenue per quarter for -1 year. The way I managed to calculate this was by using measure “Test1”. The problem is that I want to make measure “Test 2” work, how do I do it? Essentially, I want a formula that tells PowerBI to go -1 year and select the first available quarter with that of that year.

image

Revenu QC = Calculate every year`s cumulative total revenue

VAR SelectedYear = SELECTEDVALUE(‘Dim_Calendar (Q)’[Year])

Return

CALCULATE([Revenue Q],
FILTER(ALLSELECTED(‘Dim_Calendar (Q)’),
‘Dim_Calendar (Q)’[Year_Quarter]<=MAX(‘Dim_Calendar (Q)’[Year_Quarter]) &&
‘Dim_Calendar (Q)’[Year]=SelectedYear
)
)

Test 1 = Calculate the previous year`s cumulative total using an index column that is stored in the

VAR CurrentYear = SELECTEDVALUE(‘Dim_Calendar (Q)’[Year])
VAR CurrentQuarter = SELECTEDVALUE(Dim_Calendar[#Quarter])
VAR Index = Selectedvalue(‘Dim_Calendar (Q)’[Index])

VAR CurrentRevenue = CALCULATE([Revenue Q],
FILTER(ALLSELECTED(‘Dim_Calendar (Q)’),
‘Dim_Calendar (Q)’[Year_Quarter]<=MAX(‘Dim_Calendar (Q)’[Year_Quarter]) &&
‘Dim_Calendar (Q)’[Year]=CurrentYear
)
)

VAR PreviousRevenue = CALCULATE([Revenue Q],
FILTER(ALLSELECTED(‘Dim_Calendar (Q)’),
‘Dim_Calendar (Q)’[Index]<=Index-4 &&
‘Dim_Calendar (Q)’[Year]=CurrentYear-1
)
)

Return
PreviousRevenue

Test 2 = Should calculate the same thing as a measure “Test1”, but this actually calculates previous year total revenue.

VAR CurrentYear = SELECTEDVALUE(‘Dim_Calendar (Q)’[Year])

VAR CurrentRevenue = CALCULATE([Revenue Q],
FILTER(ALLSELECTED(‘Dim_Calendar (Q)’),
‘Dim_Calendar (Q)’[Year_Quarter]<=MAX(‘Dim_Calendar (Q)’[Year_Quarter]) &&
‘Dim_Calendar (Q)’[Year]=CurrentYear
)
)

VAR PreviousRevenue = CALCULATE([Revenue Q],
FILTER(ALLSELECTED(‘Dim_Calendar (Q)’),
‘Dim_Calendar (Q)’[Year_Quarter]<=MAX(‘Dim_Calendar (Q)’[Year_Quarter]) &&
‘Dim_Calendar (Q)’[Year]=CurrentYear-1
)
)

Return

PreviousRevenue

2

Hi @Roboboboberts

To help us further analyze your current state and visualize your issue, could you please provide as many as you can of:

  • Your work-in-progress PBIX file, using sanitized data if necessary
  • Your dataset as an Excel file (again, sanitized if necessary)
  • A detailed mock-up (marked-up screenshot or Excel file) of your desired outcome.

Also, if you provide DAX in your post, please format it using the built-in formatter.

Greg

_eDNA Forum - Format DAX

3

Hi @Roboboboberts, did the response provided by @Greg help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!

5

Hi @Roboboboberts, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!