Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have been trying to calculate Count based on Current Year , Fiscal year and same thing for last year.
Everything is working for Calendar Year and Fiscal Year but sameperiod last year, Month and quarters are not working for both Calendar and Fiscal Year Table.
As you can see blue highlihgted,
Calendar Year: In Calendar Year table, I am trying to calculate Count for Last year same period but you can see the value is going into 2021 which should not happend. If its same period last year then it should be before current year not next year. This has been happening for Quarters and Month also.
Fiscal year: In Fiscal Year table, I am trying to calculate Count for Last year same period but you can see the value is going into 2022 which should not happend. If its same period last year then it should be before current year not next year. This has been happening for Quarters and Month also.
NOTE: Fiscal Year starting from October to September and I have only data upto December 2020. So, 2022 should definately not come.
Formulas I have applied are follows; (Formula are working without error just values are placing in wrong place)
Last Year Same period for Year : Calculate(Count(ID), SAMEPERIODLASTYEAR('Date'[Date]))
Calculate (Count(ID), DATESYTD(dateadd('Date'[Date],-1,YEAR)))
Last Year Same Period for Quarters: Calculate(Count(ID), DatesQTD(Dateadd('Date'[Date],-1,Year)))
Last Year Same Period for Month: Calculate(Count(ID), DatesMTD(Dateadd('Date'[Date],-1,Year)))
Any suggestion and Recommendation will be appreciated!
Thanks
Solved! Go to Solution.
What you are describing is SAMEPERIODNEXTYEAR which doesn't really exist as a function, but you could use DATEADD with positive 12 month difference or ParallelPeriod with positive difference.
When we do LAST year, if we have data for 2020, the SAMEPERIODLASTYEAR measure will look for data in 2019 - if there was no data, it will be blank. That means that if our dataset has data for 2019 and 2020, your SAMEPERIODLASTYEAR measure will give you a target value for the years AFTER you have data, or the NEXT years, so 2020 and 2021. I know it's a bit counter intuitive that SAMEPERIODLASTYEAR gives results for NEXT years compared to what is in our dataset, but if you look at just 1 year data it makes a bit more sense (sometimes).
So take this example below. I have used model data that has Sales reported for 2019, and 2020 only. Because we don't know the sales for 2018, when we look at January 2019, I do have Sales for that month, but I don't know what the SAMEPERIODLASTYEAR sales were, because in 2019, LASTYEAR = 2018 and we don't have data for 2018.
When we look at the year 2020, I DO have data for the last year, which is now 2019.
When we look at the year 2021, I also already know what may sales were for the previous year, so essentially we know our target, but we don't yet have the Sales as we haven't gotten to those dates yet.
Here is using the Sales NEXT year example, which is what you are expecting:
I will also attach this sample file so you can play with the data, but if it still is unclear, let us know and maybe we can suggest videos or other way to make it clear.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
Hi @damit23183 ,
According to my understand, you want to display last year's YTD QTD,MTD,right?
Take the Year as an example, you could use the following formula after adding a Year column
Year = [Date].[Year]
this year =
IF (
HASONEVALUE ( 'Table'[Year] ),
CALCULATE (
SUM ( 'Table'[Cost] ),
FILTER ( 'Table', [Year] = MAX ( 'Table'[Year] ) )
),
CALCULATE ( SUM ( 'Table'[Cost] ), DATESQTD ( 'Table'[Date] ) )
)
YTD LY =
IF (
HASONEVALUE ( 'Table'[Year] ),
CALCULATE (
SUM ( 'Table'[Cost] ),
FILTER ( ALL ( 'Table' ), [Year] = MAX ( 'Table'[Year] ) - 1 )
)
)
My final visualization looks like this:
Did I answer your question? Please mark my reply as solution ,thank you.
If you have any questions, please upload some insensitive data samples .
Best Regards,
Eyelyn Qin
Hi,
Thanks for your response really appriciate it.
Yes you are right i would like to calculate count for Last year same period for YEAR, QUARTER and MONTH.
I did try your LAST YEAR Formula but did not work.
For current YEAR, QUARTER and MONTH calculations are working for Calendar as well as Fiscal year.
I am only struggling with sameperiod last year calculation for YEAR, QUARTER and Month.
Thanks
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
Hi,
Thanks for your response.
Appreciate your time!
I did try the formula you gave but its not giving me expected result.
My understanding is that if I am calculating Count for SAMEPERIOD LAST YEAR then for calendar I should only data up to2018, 2019 and 2020 because I only data for these two years (2019 & 2020). Please Correct me if I am wrong here on understanding?
Therefore, I should not get data for 2021, correct? (But I am getting data for full 2021 year)
Thanks
What you are describing is SAMEPERIODNEXTYEAR which doesn't really exist as a function, but you could use DATEADD with positive 12 month difference or ParallelPeriod with positive difference.
When we do LAST year, if we have data for 2020, the SAMEPERIODLASTYEAR measure will look for data in 2019 - if there was no data, it will be blank. That means that if our dataset has data for 2019 and 2020, your SAMEPERIODLASTYEAR measure will give you a target value for the years AFTER you have data, or the NEXT years, so 2020 and 2021. I know it's a bit counter intuitive that SAMEPERIODLASTYEAR gives results for NEXT years compared to what is in our dataset, but if you look at just 1 year data it makes a bit more sense (sometimes).
So take this example below. I have used model data that has Sales reported for 2019, and 2020 only. Because we don't know the sales for 2018, when we look at January 2019, I do have Sales for that month, but I don't know what the SAMEPERIODLASTYEAR sales were, because in 2019, LASTYEAR = 2018 and we don't have data for 2018.
When we look at the year 2020, I DO have data for the last year, which is now 2019.
When we look at the year 2021, I also already know what may sales were for the previous year, so essentially we know our target, but we don't yet have the Sales as we haven't gotten to those dates yet.
Here is using the Sales NEXT year example, which is what you are expecting:
I will also attach this sample file so you can play with the data, but if it still is unclear, let us know and maybe we can suggest videos or other way to make it clear.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
Hi,
THank you for the detail level information and really appriciate it.
I have mix up your logic and some from DAX Pattern which help me to resolve what I was looking for.
Thanks
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |