Sub Total on Column using DatesBetween Function not returning the right total

[VeemalS]

Hello,

I have the following 2 measures:

The first one is Total sales and 2nd one is measure defined as follows:

Total Sales First 15 days = Calculate(FactSales[Total Sales],(DATESBETWEEN(Dates[Date],FIRSTDATE(Dates[Date]),FIRSTDATE(Dates[Date]) + 14)) )

However, the Total for that column is not right. It is returning the value of first 15 days of the first month of every year.

How can we resolve this?

Thanks

Veemal

[v-jianboli-msft]

Hi @VeemalS ,

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or if you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

Refer to:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak · Accepted solution]

@VeemalS , The grand does not have date context so it will use the very first date

you can use isinscope and change the grand total with a new meausre

if(isinscope(Table[Year]),[Total Sales First 15 days] , [sales new measure] )

IsInScope - Switch measure at different levels: https://youtu.be/kh0gezKICEM

you can also try isfiltered

https://powerpivotpro.com/2013/03/hasonevalue-vs-isfiltered-vs-hasonefilter/