DATESINPERIOD gives correct figure for quarter, but not the full year

[Blaggy]

I'm using DATESINPERIOD in DAX, and it is giving me the correct, expected answer for the quarter, but it gives the latest quarter's answer for the full year as well, and I'm not sure why.

Here is my DAX:

Test Calc =

        DIVIDE(

            CALCULATE (    

                SUM('Table’[Amount]),

                DATESINPERIOD (

                    'Calendar'[Date],                                              

                    MAXX(DATEADD('Calendar'[Date],-1,QUARTER),'Calendar'[Date]),    

                    -16,                                                            

                    QUARTER                                                        

                    )

            ),16,0

        )

I've also tried the DATESBETWEEN DAX, and it is doing the same thing, where I'm getting the right answer for the quarter, but it is using the last quarter as the full year answer as well.  

[Blaggy]

Will check out.  I guess it's not something overly apparent (and overly stupid on my part) based on what you see in my DAX... which, somewhat makes me feel better about asking the question!

Thanks - will check out your videos when I free-up.  Appreciate it.

[Blaggy · Accepted solution]

Not sure what happened to my original response, but I did want to note that I figured it out, looking into what you mentioned on why PBI doesn't alwasy sum things the way you expect.  

To the extent this could be helpful to others, this was the DAX that worked for me:

Test Calc =

    SUMX(VALUES('Calendar'[Fiscal Qtr]),

        DIVIDE(

            CALCULATE (    

                SUM('Table’[Amount]),

                DATESINPERIOD (

                    'Calendar'[Date],                                              

                    MAXX(DATEADD('Calendar'[Date],-1,QUARTER),'Calendar'[Date]),    

                    -16,                                                            

                    QUARTER                                                        

                    )

            ),16,0

        )

   )

[parry2k]

@Blaggy 

Check the related videos on my YT channel that talk about the reason why you get incorrect total

Incorrect Total - Part 1

Incorrect Total - Part 2

[Blaggy]

Curious if @parry2k (or others) can provide any insight as to whether I'm not using the DATESINPERIOD logic correctly so that I get the right answer for the full year rather than just the quarterly figures.  Thanks!

[parry2k]

Sorry still not very clear, you need to share some data with an example of what your expected output is?

[Blaggy]

Does this help?  Below is the output.  Note how the matrix is showing the FYE total being the same as Q4.  Again, each quarter is calculating properly for each BU, but for whatever reason, Power BI is not adding-up the quarters to get to the full-year figure.

[Blaggy]

Thank you, @parry2k .  I have the Table with the Amounts with a date field that is linked to the Calendar table.  The calendar table has the logic for fiscal quarters and fiscal years.  So, the logic as written is providing the correct answer for the fiscal quarters, but when trying to show the fiscal year (e.g. in a matrix view), it is calculating the last quarter only, rather than calculating the answer for the full fiscal year.  

Does that help?

[parry2k]

@Blaggy because you have having max based on the calender table, you need to use different date for this, without knowing which quarter you want to show and what would be the logic, it is  hard to provide the solution.