Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
Earl40
Helper I
Helper I

Need help creating a table in PowerBi

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 

Earl40_1-1702564727773.png

 

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 

 

Earl40_0-1702564580238.png

 

 

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 

 

 

5 REPLIES 5
v-yangliu-msft
Community Support
Community Support

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.

v-yangliu-msft
Community Support
Community Support

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)

vyangliumsft_0-1702879685739.png

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.

vyangliumsft_1-1702879685742.png

 

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

 

Earl40_0-1703022168737.png

 

Idrissshatila
Super User
Super User

Hello @Earl40 

 

try adding  two    )   )     at the end of each variable



Did I answer your question? Mark my post as a solution! Appreciate your Kudos
Follow me on LinkedIn linkedIn
Vote for my Community Mobile App Idea

Proud to be a Super User!




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

Earl40_0-1702567735836.png

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.