Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi all,
This might be pretty easy for most of us around here but I'm a newbie to the POWER BI platform. Request your help for the below scenario.
I have a date column which is a date hierarchy containing
I need to show up, Year, total sale, total previous year sale, etc. I'm able to do this using CALCULATE & SAMEPERIODLASTYEAR function.
But the slicer for Year should show values as FY2020, FY2019, FY2018 for which I've to create a new measure which has resulted in the loss of Time intelligence & the functions used above are not working anymore.
Please let me know if there is any other way of showing up FY2020, FY2019, etc.. & if this is the only way, how can I handle to show the required matrix?
Thanks in advance!
A new measure or column? Can you just create a new column in your table, "FY" & [Year] and use that in your slicer?
@Greg_DecklerYes, I'm having "FY"&"[Year]" as a column in the table. That's working fine on the slicer but the time intelligence functions do not work anymore.
This FY & year is in date table ?
And you marked it as date table. Also, Make sure slicer is from date table column.
Totalytd or datesytd should work
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))
This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR('Date'[Date])),"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
That's weird. Can you post sample data and formulas? Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...
I apologize for the delayed response. Please allow me to describe the situation as below.
The data is in the below format:
Date | Sales | Label | Year | FY Year |
2020-01-01 | 3 | A | 2020 | FY 2020 |
2020-02-01 | 4 | A | 2020 | FY 2020 |
2020-01-01 | 3 | B | 2020 | FY 2020 |
2020-02-01 | 4 | B | 2020 | FY 2020 |
2019-03-01 | 5 | A | 2019 | FY 2019 |
2019-02-01 | 1 | A | 2019 | FY 2019 |
2018-02-01 | 3 | A | 2018 | FY 2018 |
2018-04-01 | 2 | A | 2018 | FY 2018 |
2019-03-01 | 5 | B | 2019 | FY 2019 |
2019-02-01 | 1 | B | 2019 | FY 2019 |
2018-02-01 | 3 | B | 2018 | FY 2018 |
2018-04-01 | 2 | B | 2018 | FY 2018 |
The 'FY Year' is a custom column. Now, I want a slicer on 'FY Year' & a table showing current year sales & previous year sales. But when I use 'Year' on the slicer, everything works fine but If I use 'FY Year' on a slicer, previous year sales column turn blank. The required table if the 'FY Year' is selected as 2019 can be seen below:
Label | This Year Sales | Previous Year Sales |
A | 6 | 5 |
B | 6 | 5 |
The formulas used to create the measures are:
Please assist on how we can handle this.
Thank you!