Hi all,
I am trying to make a clustered bar graph showing the count of tickets (each is a row in the table, with a date and other info in the columns) YTD vs last years YTD for the same time period. For example, row count from 1/1/24 to the end of the dataset, and 1/1/23 to the last date of the data set minus 1 year. I have attempted a few measures but have been unsucessful. Below is the desired result.
1 ACCEPTED SOLUTION
Hi @wiselyman3 ,
According to your description, here are my steps you can follow as a solution.
(1) This is my test data.
(2) We can create measures.
this year = COUNTROWS(FILTER(ALLSELECTED('Table'),'Table'[Date]<=MAX('Table'[Date])))last year =
var _last=DATE(YEAR(MAX('Table'[Date]))-1,MONTH(MAX('Table'[Date])),DAY(MAX('Table'[Date])))
RETURN COUNTROWS(FILTER(ALLSELECTED('Table'),'Table'[Date]<=_last && YEAR('Table'[Date])=YEAR(MAX('Table'[Date]))-1) )(3) Then the result is as follows.
If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @wiselyman3 ,
According to your description, here are my steps you can follow as a solution.
(1) This is my test data.
(2) We can create measures.
this year = COUNTROWS(FILTER(ALLSELECTED('Table'),'Table'[Date]<=MAX('Table'[Date])))last year =
var _last=DATE(YEAR(MAX('Table'[Date]))-1,MONTH(MAX('Table'[Date])),DAY(MAX('Table'[Date])))
RETURN COUNTROWS(FILTER(ALLSELECTED('Table'),'Table'[Date]<=_last && YEAR('Table'[Date])=YEAR(MAX('Table'[Date]))-1) )(3) Then the result is as follows.
If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@v-tangjie-msft here is sample data. Using this data, we can see that the counts for sold items in 2024 (1/1/24 to 5/14/24) so far are are as follows: 7 Apples, 2 Bananas, 3 Oranges. In the same time frame for 2023 (1/1/23 to 5/14/23) the count of sold items was 8 Apples, 4 Bananas, 2 Oranges. I would like the clustered bar chart to show these counts, clustered by product so I can compare the counts from this time last year. The X axis is the product, and the Y Axis values are "this year" count and "last year" count. Hopefully this helps
Hello Neeko @v-tangjie-msft
This is close but not the solution currently. I am looking to compare last years data to this years data of the same time period. For example, "this year" data range stops on May 15th 2024. The range is 1/1/24 to 5/15/24. I would like the bar graph columns for "last year" to also reflect this time frame, minus 1 year (1/1/23 to 5/15/23), and actively change when new data is added for this year.
Hi @wiselyman3
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
