old news

[Olamum]

Unknown

[Ashish_Mathur · Accepted solution]

Hi,

Please find attached my PBI file.

Hope this helps.

[Olamum · Accepted solution]

Thank you so much

[Ashish_Mathur · Accepted solution]

You are welcome.

[Ashish_Mathur · Accepted solution]

Those have been subsumed into the single Date column.  Study the transformation steps in the Query Editor carefully.

[Ashish_Mathur]

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.

[Olamum]

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 

[Ashish_Mathur · Accepted solution]

Hi,

Please find attached my PBI file.

Hope this helps.

[Olamum]

 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

[Ashish_Mathur]

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.

[Olamum]

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 

[Ashish_Mathur]

What exactly are you trying to say.  I am completed lost.

[Olamum]

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.

[Ashish_Mathur]

I really cannot understand.  May be someone else will help you.

[Olamum]

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

[Ashish_Mathur · Accepted solution]

Those have been subsumed into the single Date column.  Study the transformation steps in the Query Editor carefully.

[Olamum]

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"

[Ashish_Mathur]

I do not know what mistake you are committing.  As you can see, it works fine in my file.  Please recheck thoroughly.

[Olamum · Accepted solution]

Thanks

[Olamum · Accepted solution]

Thank you so much

[Ashish_Mathur · Accepted solution]

You are welcome.

[Olamum]

@Ashish_Mathur