Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hello,
I currently have a PowerPoint table thats manually updated and need to create it in PowerBi. The goal is to create a table that shows the % of items aged >30 days for the notification type in the last 3 months (it 99% of the time going to be 0%). The end result should look something like this
I started by first trying to create the first part (was gonna add in the mon ths part later) which is determine %. I tried to create a measure that counts the # of notification items aged > 30 days divided by the total # of notification. My formula isnt working and unsure why (screen shot below) . The next step would then be figuring out the month filter. I have attached
I posted the sample report and dataset on drop box
Data Excel- https://www.dropbox.com/scl/fi/d8fj5553qaxtyhxmxl6m2/Data-Example.xlsx?rlkey=nsm8xvwbv3u1umkk48ho2ca...
Thank you in advance
Hi @Earl40 ,
ADDCOLUMNS(
_table1,"Year",YEAR([Date]),"Month",FORMAT([Date],"mmmm"))
ADDCOLUMNS function means:
Adds calculated columns to the given table or table expression.
ADDCOLUMNS function (DAX) - DAX | Microsoft Learn
The function _Table1 creates a table of dates in the interval 2023.1.1 - 2023.12.31.
"Year",YEAR([Date]): represents the creation of a column with the name Year, and the data inside is the Year of each date.
"Month",FORMAT([Date], "mmmm")): represents the creation of a column, the column name is Month, the data inside is the month of each date, the month is in the format of "mmmm".
Format () function means:
Converts a value to text according to the specified format.
FORMAT function (DAX) - DAX | Microsoft Learn
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Earl40 ,
Here are the steps you can follow:
1. Create calculated table.
Main_Table =
var _table1=
CALENDAR(
DATE(2023,1,1),DATE(2023,12,31))
var _table2=
ADDCOLUMNS(
_table1,"Year",YEAR([Date]),"Month",FORMAT([Date],"mmmm"))
var _table3=
{"Notification closed aged > 30 Days"}
var _table4=
SUMMARIZE(_table2,[Year],[Month])
return
CROSSJOIN(
_table4,_table3)
2. Create measure.
Test =
CALCULATE(
DISTINCTCOUNT('Data Table'[Item #]),
FILTER(ALL('Data Table'),
YEAR('Data Table'[Create Date])=MAX('Main_Table'[Year])&&FORMAT('Data Table'[Create Date],"mmmm")=MAX('Main_Table'[Month])&&'Data Table'[Age]>=30&&'Data Table'[Type]="Notification"))
3. Placement in Matrix Visual.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
@v-yangliu-msft Thanks, quick question which column field from the table should be added. I highlighted the section in the screen shot below.
Thank You
Hello @Earl40
try adding two ) ) at the end of each variable
Proud to be a Super User! | |
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
116 | |
108 | |
107 | |
93 | |
70 |
User | Count |
---|---|
173 | |
135 | |
131 | |
96 | |
94 |