The total for same period last year is incorrect

[Vettesnack]

Hi Guys,

I am new to Power BI and I have some issues with my current dashboard.

Please see the screen shot below:

In the current year for two dates there was no revenue registered for two dates 16-1-22 and 23-1-22. I did some research on the Power Bi forum on how to solve this, but I did not manage to find how I can solve this type of issue. The "Omzet YTD Last Year" is the correct. 

The DAX formulas which I use are the following:

Omzet Last Year =

CALCULATE([0. Bruto Omzet], dateadd('Date Table'[Datum],-1,year))  --- SAMEPERIODLASTYEAR() gives the same issue

Omzet Vorig Jaar =

VAR DataMaxDate =

CALCULATE ( MAX ( 'Sales Orders Full'[Order Date] ), ALL ( 'Sales Orders Full') )

RETURN

CALCULATE (

SUMX( 'Sales Orders Full', 'Sales Orders Full'[Gross_Revenue]),

SAMEPERIODLASTYEAR(

INTERSECT (

VALUES ( 'Date Table'[Datum] ),

DATESBETWEEN ( 'Sales Orders Full'[Order Date] , BLANK (), DataMaxDate )

)

)

)

I use a date table with the first date in the fact table untill today. This table is marked as date table. 

Can someone please explain to me:

Why the values in the field Omzet Last Year are correct but the total sum is incorrect?

Why the values in the field Omzet Vorig Jaar misses the 4036 on 16-1 in the total sum?

How I can solve this issue in the future?

I would be verry gratefull if someone can explain my problem and help me to solve this. 

[TheoC · Accepted solution]

Hi @Vettesnack 

For question 1, I think this post by @Greg_Deckler explains the issue you are having: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

For question 2, it may have something to do with the nature / way you are using the second measure.  If you refer to the above blog by Greg, you should find it resolves the first question and nullifies the second question.  

Hope this helps!

Theo 🙂

[Vettesnack]

Thanks a lot Theo this explains my problem