Date Range Slicer giving me the wrong values for 1 July

[_Amy_]

Hi All,

I hope someone can help me with this.

I have built a Power BI report which has each of our clients, and then shows the Billed Fees in one column and the Billed Fees for the same period in the prior year in the next column, like this:

Client Name	Billed Fees	Billed Fees Prior Year
ABC	$100	$75
XYZ	$150	$110

I have a date slicer with the date range.

The problem I am having is with the Billed Fees column.

When I select the date range of 1 July 2021 to 30 June 2022 (FY22), it gives me the Billed Fees for FY22, and it also adds on the billed fees for FY21, so effectively giving me billed fees for two years  (1 July 2020 to 30 June 2022).

When I change the date range in the slicer to 2 July 2021 to 30 June 2022, it gives me the correct value for FY22.

Why is it adding the Billed Fees for FY21 into 1 July 2021???

And how can I stop this happening?  

[_Amy_ · Accepted solution]

I contacted Microsoft Support with this and they found there was a bug.

The issue was with the date filter I was using on my slicer.

Changing the date filter to the page rather than the slicer fixed the issue.

[lukiz84]

So i assume that for 2 July ongoing there are no fees until 22? Thats why it "works".

But all your measure does is sum up all the values for the given dates. And because you select July 21 to June 22 it sums all of those numbers. Thats correct and not a bug.

Why should PowerBI know that you just want to see FY22 numbers?

Use TOTALYD([yourMeasure]) and place the Year column of your date table on the matrix, then it works.

[_Amy_]

Hello,

Thanks for your reply.

There are billed fees coming through for most days in July 2021, (which is in the FY22 financial year) eg: 

5/07/2021	$261,369
6/07/2021	$179,987
7/7/2021	$17,192
8/7/2021	$10,315

The financial year FY22 runs from 1 July 2021 to 30 June 2022.

We are currently in the FY23 which runs from 1 July 2022 to 30 June 2023.

The problem is that it is adding the FY21 financial year numbers in when I select 1 July 2022 in my date slicer.

The FY21 finanical year runs from 1 July 2020 to 30 June 2021 so selecting 1 July 2022 should not include these numbers.

I don't think the measure TOTALYD is flexible enough for what I need. I want the users to select any date range in the slicer (which is from 1 July 2021 to 30 June 2023) and it will show them the billed fees for those dates in the time period selected and for the same period in the prior year. E.g. it could be for the past 3 months, 6 months or other specified date range.

It all works fine except when I select 1 July 2021 which is the first date available to select in the slicer and it adds all the prior year's numbers... 

I agree I need to add something in the measure to tell it to look at the dates in my slicer but TOTALYTD is not it.

Thank you!

[lukiz84]

oh my bad, I thought  FY was Full Year, not Financial Year - let me get back to that later, we will find a solution 🙂

Please checkout this video, it's explained there Switch Between Calendar and Fiscal Year in Power BI - YouTube

[lukiz84]

Please post the measure you used.

[_Amy_]

It's just =SUM(Table Name [Billed Fees])