Forum Discussion
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"))
CYPY=IF(YEAR(Date[Date])=YEAR(TODAY())-1,"PY",
IF(YEAR(Date[Date])=YEAR(TODAY()),"CY","Others"))
Here I am getting Sales value as blank, ideally, the value should show for sales as Jan 2021 is not completed as per business and it shows to compare the year of Dec 2020 and Dec 2020 should be my current Year "CY".
I want to show Dec 2020 as CY as I will be running the report in Jan 2021 and Jan 2021 is not yet completed so Dec 2020 will be my CY but when I will be in Feb 2021 and Feb 2021 is not completed and I am running my report, in that case, Jan 2021 will act as CY.
I am not able to handle year-end issues
6 Replies
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.⚡
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 CYWhen I will run my report in Feb 2021
Month, Year, CYPY
Dec 2020 PY
Jan 2021 CYFEB 2021 CY
Note: report where displaying correct data during Jan 20 -Dec 20, but it starts failing in the month of Jan 21
amikm I have a post which talks about delayed month end, you can take the similar approach delayed year-end or you can build the logic for CY that in Feb it take the current year as CY otherwise it should take the previous year as CY.
Hi amikm ,
You could use the following formula to modify the CYPY logic:
CYPY = IF ( MONTH ( TODAY () ) > 1, IF ( YEAR ( [Date] ) = YEAR ( TODAY () ) - 1, "PY", "CY" ), IF ( YEAR ( [Date].[Date] ) = YEAR ( TODAY () ) - 1 && MONTH ( [Date] ) = 12 || YEAR ( [Date] ) = YEAR ( TODAY () ), "CY", "PY" ) )Here is the pbix file.
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hi amikm ,
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 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 from your Onedrive for Business after removing sensitive data.
Best Regards,
Eyelyn Qin
