Forum Discussion
Help with report visual
Greetings power bi friends,
I have a dataset that looks similar to this:
| App ID | Created Date | Approved Date | Cancelled Date | Completed Date |
| 001 | 1/5/2023 | 1/6/2023 | 1/10/2023 | |
| 002 | 1/6/2023 | 1/8/2023 | ||
| 003 | 1/10/2023 | 1/11/2023 | ||
| 004 | 1/12/2023 |
My goal is to create a report visual of the data such that it looks like this:
| Summary | This Month | Last Month | Year to Date | Inception to Date |
| Created | 100 | 120 | 540 | 800 |
| Approved | 60 | 80 | 320 | 590 |
| Cancelled | 50 | 50 | 250 | 350 |
| Completed | 12 | 16 | 64 | 118 |
*Where each value in the table is a count of "App ID"
Do you have a solution? Please advise how best to approach this. Thank you,
Hi Ibendlin, thank you for the reply. I figured out my solution by using the Unpivot Columns feature in Data Query.
In summary, I duplicated my original data query, removed unecessary columns, selected the date columns and unpivoted them so that I had a new table with three columns: the [App ID] column, an attribute column representing the date name, and a value column representing the date value. I was then able to create a relationship between the original data query and the duplicate query using the Relationship Manager in the Model section of Power BI desktop.
Following that, I created several measures to count MTD, LMTD, YTD, and ITD based on the duplicate table's data.
If someone else stumbles upon this post and is facing a similar challenge, feel free to reply or message me and I'd be happy to go into more detail.
2 Replies
Please provide sanitized sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.What does "Year" mean? Calendar year?
What does "Inception" mean?
Hi Ibendlin, thank you for the reply. I figured out my solution by using the Unpivot Columns feature in Data Query.
In summary, I duplicated my original data query, removed unecessary columns, selected the date columns and unpivoted them so that I had a new table with three columns: the [App ID] column, an attribute column representing the date name, and a value column representing the date value. I was then able to create a relationship between the original data query and the duplicate query using the Relationship Manager in the Model section of Power BI desktop.
Following that, I created several measures to count MTD, LMTD, YTD, and ITD based on the duplicate table's data.
If someone else stumbles upon this post and is facing a similar challenge, feel free to reply or message me and I'd be happy to go into more detail.
