DATESBETWEEN with ALL Function

[amaniramahi]

I need to calculate Year Over Year results for the same periods

I used the following code

YOY% = 
var UptoDate = MAX('Monthly Reg Data'[Sales Month])
var CY = Year(UptoDate)
var PY = CY-1
var CYSales = 
CALCULATE(
    SUM('Monthly Reg Data'[Qty]),
    DATESBETWEEN('Monthly Reg Data'[Sales Month],DATE(CY,1,1),UptoDate)
)
var PYSales = 
CALCULATE(
    SUM('Monthly Reg Data'[Qty]),
    FILTER(ALL('Monthly Reg Data'[Sales Month]),YEAR('Monthly Reg Data'[Sales Month])=PY),
    DATESBETWEEN('Monthly Reg Data'[Sales Month],DATE(PY,1,1),DATE(PY,MONTH(UptoDate),1))
)
return
CYSales/PYSales

 but it keeps returning PYSales as blank, because the filter I use for Sales Month (which is a Date Column)

I tried to use ALL function to undo the filter effect on the PYSales Calculation portion only but it didnt work.

Please Help 😞 

[amaniramahi · Accepted solution]

Sorry, It was my mistake 

I figured it out

[Mikelytics]

Hi @amaniramahi ,

Did you try working with TOTALYTD and SAMEPERIODLASTYEAR?

totalYTD = TOTALYTD(SUM(fact_table_m[Sales]),'Date'[Date],ALL('Date'[Date]),"12/31")

total_YTD_previous_year = TOTALYTD(SUM(fact_table_m[Sales]),SAMEPERIODLASTYEAR('Date'[Date]),ALL('Date'[Date]),"12/31")

 I hope this is the expected result.

Best regards

Mikelytics

Did I solve your request? PLease mark my post as solution.

Appreciate your Kudos.

[amaniramahi]

Thank you but that did not solve the problem.

I still have PYSales as Blank

Date column i am using in time int. functions is the same column i am using in the the slicer.

so I need to remove its effect on PYSales variable calculation only using ALL function but I stil dont know how to use it.

[parry2k]

@amaniramahi for any time intelligence add date dimension in your model and then perform all-time intelligence calculation from that table and that is the issue with your measure. You can add date dimension using my blog post.

Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡