Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello everyone.
I'm still new to PowerBI, and I know somebody might have already posted regarding this topic, but I'm still confused.
Please help me with this.
My dataset contains NC ID and NC Open Date (from 2021 to 2022). Now, I want to know how many NCs are opened each month and save the total number like below. So that I can union all the metrics to a monthly metrics table.
2021Jan | 2021Feb | ... 2022Aug | |
NC Opened MTD | 10 | 15 | ... 0 |
When I apply the formula, it's all 1, how can I get the total? I have a calendar table, but I don't know how to use it. Please help,
thanks in advance!
Solved! Go to Solution.
@Anonymous , Create a date only column
date = datevalue([NC Open Date])
Join it with table having month, etc
Addcolumns(calendar(date(2020,01,01), date(2021,12,31) ), "Month no" , month([date])
, "Year", year([date])
, "Month Year", format([date],"mmm-yyyy")
, "Month year sort", year([date])*100 + month([date])
, "Qtr Year", format([date],"yyyy-\QQ")
, "Qtr", quarter([date])
, "Month",FORMAT([Date],"mmmm")
, "Month sort", month([DAte])
)
Plot with help from date table
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |