The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi all,
I have data like the image below
I want to show Charges based on year, Quarter, & Month view
Expected Result
Year View
Quarter View
Same as monthly view, want to show
Data
ID | Start Date | End Date | No Month Active | Charge Per Month |
ID_1 | 1 January 2023 | 1 January 2025 | 24 | 10 |
ID_2 | 15 February 2023 | 15 February 2026 | 36 | 20 |
ID_3 | 20 May 2023 | 20 May 2025 | 24 | 30 |
ID_4 | 20 May 2023 | 20 May 2024 | 12 | 10 |
Solved! Go to Solution.
hi @vengadesh_p
Let's say YourData is your table,
Create a Calculated table
ChargesByMonth1 =
ADDCOLUMNS (
GENERATE (
YourData,
VAR StartDate = DATE(YEAR([Start Date]), MONTH([Start Date]), 1)
VAR MonthsCount = [No Month Active]
RETURN
ADDCOLUMNS (
GENERATESERIES ( 0, MonthsCount - 1, 1 ),
"MonthDate", EDATE(StartDate, [Value]),
"Charge", [Charge Per Month]
)
),
"Year", YEAR([MonthDate]),
"Quarter", "Q" & FORMAT(QUARTER([MonthDate]), "0"),
"YearQuarter", FORMAT([MonthDate], "YYYY") & " Q" & QUARTER([MonthDate])
)
use it in a matrix table
Hi @vengadesh_p ,
Thankyou @kushanNa for your reply on the issue.
Has your issue been resolved? If the response provided by the community member addressed your concern, kindly confirm.
Marking it as Accept Answer and give us Kudos if you found it helpful allows us to ensure that the solutions shared are valuable for the entire community.
If you have any further questions, feel free to reach out!
Thank you for your cooperation!
hi @vengadesh_p
Let's say YourData is your table,
Create a Calculated table
ChargesByMonth1 =
ADDCOLUMNS (
GENERATE (
YourData,
VAR StartDate = DATE(YEAR([Start Date]), MONTH([Start Date]), 1)
VAR MonthsCount = [No Month Active]
RETURN
ADDCOLUMNS (
GENERATESERIES ( 0, MonthsCount - 1, 1 ),
"MonthDate", EDATE(StartDate, [Value]),
"Charge", [Charge Per Month]
)
),
"Year", YEAR([MonthDate]),
"Quarter", "Q" & FORMAT(QUARTER([MonthDate]), "0"),
"YearQuarter", FORMAT([MonthDate], "YYYY") & " Q" & QUARTER([MonthDate])
)
use it in a matrix table
User | Count |
---|---|
77 | |
76 | |
36 | |
31 | |
29 |
User | Count |
---|---|
93 | |
79 | |
57 | |
48 | |
48 |