Forum Discussion

amikm's avatar
amikm
Icon for Helper V rankHelper V
5 years ago

Issue with Date during closed year

I have a table that has all the dates from 01/01/2020 to  31/12/2020 I am writing a DAX measure to calculate Sales based on dates Sales=CALCULATE(SUM(Sales[Sale]), FILTER(Sales,Sales[CYPY]="CY")) ...
Need Help
parry2k's avatar
parry2k
Icon for Super User rankSuper User
5 years ago

amikm first and foremost, anytime you want to work with dates, you must add a date dimension into your model. This is important and must be part of your model. There are many posts on how you can add one but here is one of my posts.

 

Once the date dimension is added, you can have this CY and PY column in your date dimension and then write the following measure

 

Sales=
CALCULATE(
SUM(Sales[Sale]),
Date[CYPY]="CY")
)

 

Follow best practices, it will save your time and help to develop a scalable solution.

 

Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020

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

 

  • amikm's avatar
    amikm
    Icon for Helper V rankHelper V
    5 years ago

    Hi parry2k ,

     

    Yeah, actually the challenge is with CYPY logic, where, I want to display Dec 2020 as current year as per business logic, I am using YEAR(TODAY()) and it is giving me 2021, that might be true If I will be running my report in mid of Feb 2021, in that case, Jan 2021 data should show.

     

    But when I am running my report in mid of Jan 2021 I want to show Dec 2020 data (as jan 21) is not completed.

    I am not able to handle year-end situations, 

    When I am running my report in Jan 2021

    Month, Year, CYPY

    Dec 2020 CY
    Jan 2021 CY

     

    When I will run my report in Feb 2021

    Month, Year, CYPY

    Dec 2020 PY
    Jan 2021 CY

    FEB 2021 CY

     

    Note: report where displaying correct data during Jan 20 -Dec 20, but it starts failing in the month of Jan 21