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 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!
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 |
---|---|
118 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |