Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi,
Please find attached my PBI file.
Hope this helps.
You are welcome.
Those have been subsumed into the single Date column. Study the transformation steps in the Query Editor carefully.
Hi,
There are a few cases in your data where the end date is less than the beginning date. That is obviously not possible. Make corrections there and share the download link of the revised file.
Hello,
Please find updated data
https://drive.google.com/file/d/1T-rSkz3Aw_j7T5QR-yDvIwkGQlEyh6iG/view?usp=drive_link
I want to be able to see total count of client that served within a fiscal year example 2020-2021 in a table along with other dimensions.
And be able to see in cards, count of client that carried over service to another fiscal year(i.e end date was not in fiscal year 2020-2021)
no matter the slicer choosen fiscal date, there should be count of clients that carried over to
2021-2022
2022-2023 and etc.
Thanks
Hello,
I validated the solution by placing start date and end date side by side. if you look at the picture, the start date was 2/3/2020 which falls under fiscal year 2019-2020
But it ended in another fiscal year 2020-2021 and it was not counted. For every year the service is still on, it should be counted.
Thanks
I obviously cannot make any good use of a picture. Anyways, ensure that there is the FY calculated column formula in the Calendar Table is correct.
Certainly, it exists. Can you reverse the initial date and final date so I can verify and return it to you? This is because I'm unable to test it since the information cannot be extracted from the columns.
I mean instead of removing it, you should have it to test the values. Thanks
What exactly are you trying to say. I am completed lost.
I apologize for any confusion. To clarify, the initial file labeled "customer" that you provided cannot be verified for accuracy on my end. The method I can use to validate it, similar to the image I shared earlier, involves combining the start and end tables into a single table. By manipulating the fiscal year values, I can assess the accuracy of the numbers.
I really cannot understand. May be someone else will help you.
Quick question
What about the start and end date field? I didn't see it in the data table. I can't get the calculation done in the Power query
Those have been subsumed into the single Date column. Study the transformation steps in the Query Editor carefully.
Hello ,
I'm not getting the right answer. Count of id in fiscal year is lower than carried over
transform editor
{"[spc_startdateserviceplan]", Date.StartOfMonth, Date.Type}
}
)),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Calculated End of Month", each (
{
{"[spc_enddateserviceplan]", Date.EndOfMonth, Date.Type}
}
)),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "Month Span", each (12 * (Date.Year(Date.From([End date])) - Date.Year(Date.From([spc_startdateserviceplan]))))
+ (Date.Month(Date.From([End date])) - Date.Month(Date.From([spc_startdateserviceplan])))
+ (if Date.Day(Date.From([End date])) < Date.Day(Date.From([spc_startdateserviceplan]))
then -1
else 0
)
+ 1),
#"Added Custom4" = Table.AddColumn(#"Added Custom3", "Month List", each List.Numbers(1,
[Month Span])),
#"Replaced Errors" = Table.ReplaceErrorValues(#"Added Custom4", {{"Month List", null}}),
#"Expanded Month List" = Table.ExpandListColumn(#"Replaced Errors", "Month List"),
#"Changed Type2" = Table.TransformColumnTypes(#"Expanded Month List",{{"End date", type date}}),
#"Added Custom5" = Table.AddColumn(#"Changed Type2", "Date", each Date.StartOfMonth(Date.AddMonths(
[End date],
0 -[Month Span] + [Month List]
))),
#"Replaced Errors1" = Table.ReplaceErrorValues(#"Added Custom5", {{"Date", null}}),
#"Removed Columns" = Table.RemoveColumns(#"Replaced Errors1",{"spc_enddateserviceplan", "spc_startdateserviceplan", "End date", "Month Span", "Month List"}),
#"Changed Type3" = Table.TransformColumnTypes(#"Removed Columns",{{"Date", type date}})
in
#"Changed Type3"
I do not know what mistake you are committing. As you can see, it works fine in my file. Please recheck thoroughly.
Thanks
Thank you so much
You are welcome.
User | Count |
---|---|
87 | |
84 | |
67 | |
62 | |
57 |
User | Count |
---|---|
138 | |
112 | |
94 | |
82 | |
71 |