Forum Discussion

amaniramahi's avatar
amaniramahi
Helper V
5 years ago
Solved

DATESBETWEEN with ALL Function

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 😞 

Need Help

4 Replies

  • parry2k's avatar
    parry2k
    Super User
    5 years ago

    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.⚡

     

     

     

  • Mikelytics's avatar
    Mikelytics
    Resident Rockstar
    5 years ago

    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's avatar
      amaniramahi
      Helper V
      5 years ago

      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.