cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Mikail
Frequent Visitor

R12 Calculation Error

Hello everyone, I am trying to calculate R12 for past 5 years. I am filtering my data based on Date lookup table. The max or latest date is 01/07/2021(dd/mm/yyyy). When I choose this latest date(July) from slicer I was expecting to see 01/08/2020. However calculation returned just blank. However when I choose  June calculation is correct. Is there anyone to help to solve the issue?

 

Month.PNGYear.PNGMonth2.PNGYear2.PNG

 

The Dax Code:

R12 IND =
Var SelectedMaxDate = MAX ('Date Lookup'[Date])
Var MinDate =
CALCULATE (
MIN ( 'Date Lookup'[Date]),
FILTER (
ALL ('Date Lookup'),
DATEADD (
'Date Lookup'[Date],
1,
YEAR
) > SelectedMaxDate
)
)
Return
MinDate
 
Thanks in advance!
1 ACCEPTED SOLUTION
selimovd
Super User
Super User

Hey @Mikail ,

 

I'm not really sure if the approach makes sense as DATEADD just changes the dates in the column. I think what you are looking for is the function DATESINPERIOD. Try the following approach:

R12 IND =
VAR SelectedMaxDate = MAX( 'Date Lookup'[Date] )
VAR MinDate =
    CALCULATE(
        MIN( 'Date Lookup'[Date] ),
        DATESINPERIOD(
            'Date Lookup'[Date],
            SelectedMaxDate,
            -1,
            YEAR
        )
    )
RETURN
    MinDate

  

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 

View solution in original post

2 REPLIES 2
selimovd
Super User
Super User

Hey @Mikail ,

 

I'm not really sure if the approach makes sense as DATEADD just changes the dates in the column. I think what you are looking for is the function DATESINPERIOD. Try the following approach:

R12 IND =
VAR SelectedMaxDate = MAX( 'Date Lookup'[Date] )
VAR MinDate =
    CALCULATE(
        MIN( 'Date Lookup'[Date] ),
        DATESINPERIOD(
            'Date Lookup'[Date],
            SelectedMaxDate,
            -1,
            YEAR
        )
    )
RETURN
    MinDate

  

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 
Mikail
Frequent Visitor

Hello Denis, thanks a lot for your solution! It worked 😊

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through June10th!

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it Out!

Join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!

Top Kudoed Authors