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 a data table which consists of records for over 3 years. I am using custom years where the financial year starts in Apr and ends in March. I have a measure which sums the column (traffic) and I would like to create a visual where I can see the traffic figures for each entity over several years (like the visual below).
The issue I am having is, as 2021 is not a complete year, the results showing are hard to compare. I want the traffic to be calculated to date. Meaning, I would like 2019, 2020 and 2021 traffic to be displaying for the exact same date range except one year prior. So, if the last date for traffic in my data is 16th April 2020 I want;
- 2019 to be between 1st Apr 2018 - 16th Apr 2018
- 2020 to be between 1st Apr 2019 - 16th Apr 2019
- 2021 to be between 1st Apr 2020 - 16th Apr 2020
The graph is currently set up with name, month and weeknum in the axis, year in legend, and total traffic in value.
Thanks
@Anonymous
In case you have selected 16 April 2020 on-page. You can try totalYTD or datesytd
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"3/31"))
This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR('Date'[Date])),"3/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"3/31"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"3/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"3/31"))
@Anonymous ,
You can also try
YTD Sales =
var _max = today()
return
CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"3/31"),'Date'[Date]<= _max)
Last YTD Sales =
var _max = date(year(today())-1,month(today()),day(today()))
return
CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"3/31"),'Date'[Date]<= _max)
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 |
---|---|
112 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |