Need help creating a table in PowerBi

[Earl40]

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...

PowerBi - https://www.dropbox.com/scl/fi/o8fpb6flmci8xddlfh7ig/Sample.pbix?rlkey=uhnwsilk0ty4mtodfgghssi1p&dl=...

Thank you in advance 

[Anonymous]

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.

[Anonymous]

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

[Earl40]

@Anonymous  Thanks, quick question which column field from the table should be added. I highlighted the section in the screen shot below.

Thank You

[Idrissshatila]

Hello @Earl40 

try adding  two    )   )     at the end of each variable

[Earl40]

@Idrissshatila  I tried and now it says too few arguments passed through filter