Incorrect Measure Totals

[JJP1]

Hello. 

I am seeking assistance with Power BI desktop. 

The issue I am having is that the incorrect totals are being pulled from the raw data source. See below. 

The '24' at the top of the list should be 25 and the '41' should be 42. When I review the raw data, I should be getting 190; however, Power BI only recognizes 188. I have a similar issue with another table where the grand total pulled from the raw data should be 218, but Power BI only recognizes 217. 

The tables are quite simple - they are tables totalling the # of projects after a fiscal year filter is applied. 

Any help would be greatly appreciated. 

[VijayP]

@JJP1 I have created a model look at that  https://drive.google.com/file/d/1n5J3_-EQIP5b_t9PgvYmR1NA_862YxGS/view?usp=sharing

[JJP1 · Accepted solution]

Hello @VijayP, 

I am unable to access the folder using my work laptop; however, using the following FY formula in my Dates Table, the issue appears to be resolved (so far):

FY =

var _year = if(month([date]) >=4 , year([date]),year([date])-1)

var _month = MONTH([Date])

return ("FY "&_year&"-"&(_year+1))

Thank you for all of your previous support!

[VijayP]

@JJP1 

Fiscal year formula ( A part of your date table) 
if(monthnumber<4,monthnumber+9, monthnumber-3) it gives you the fiscal month

if(monthnumber<4,year-1,year+1 ) it gives you the right year as per fiscal calender

Rest I can help only when i can see the sample data

[JJP1]

Hello @VijayP, 

I have included sample data for two fiscal years 2022-2023 and 2023-2024. 

code  date

AS	27-Oct-22
MS	28-Oct-22
JC	26-Oct-22
MS	3-Nov-22
JC	28-Oct-22
MA	11-Nov-22
JC	4-Nov-22
JC	3-Nov-22
HL	10-Nov-22
MS	9-Nov-22
JC	10-Nov-22
AS	9-Dec-22
JC	10-Nov-22
MS	21-Nov-22
JC	18-Nov-22
GS	18-Nov-22
HL	17-Nov-22
MS	24-Nov-22
MS	24-Nov-22
JC	2-Dec-22
JC	2-Dec-22
GS	2-Dec-22
MS	9-Dec-22
GS	9-Dec-22
HL	16-Dec-22
AS	16-Dec-22
MS	16-Dec-22
GS	9-Dec-22
GS	23-Dec-22
MS	20-Dec-22
GS	23-Dec-22
HL	5-Jan-23
AS	22-Dec-22
GS	13-Jan-23
AS	12-Jan-23
HL	12-Jan-23
AS	20-Jan-23
HL	20-Jan-23
AS	20-Jan-23
MS	19-Jan-23
MS	18-Jan-23
GS	20-Jan-23
AS	25-Jan-23
GS	14-Feb-23
HL	2-Feb-23
GS	19-Jan-23
MS	1-Feb-23
HL	1-Feb-23
MS	3-Feb-23
HL	3-Feb-23
PA	31-Jan-23
PA	2-Feb-23
MS	2-Feb-23
JC	3-Feb-23
MS	8-Feb-23
SR	10-Feb-23
GS	23-Feb-23
PA	15-Feb-23
JC	15-Feb-23
JC	17-Feb-23
PA	17-Feb-23
JC	15-Feb-23
JC	17-Feb-23
GS	24-Feb-23
PA	22-Feb-23
GS	24-Feb-23
JC	22-Feb-23
PA	23-Feb-23
SR	24-Feb-23
JC	3-Mar-23
GS	2-Mar-23
JC	1-Mar-23
SR	3-Mar-23
JC	2-Mar-23
JC	3-Mar-23
GS	10-Mar-23
GS	10-Mar-23
SR	17-Mar-23
JC	15-Mar-23
GS	22-Mar-23
SR	24-Mar-23
JC	24-Mar-23
GS	31-Mar-23
JC	28-Mar-23
GS	29-Mar-23
JC	29-Mar-23
SR	31-Mar-23
JC	30-Mar-23
HL	25-Apr-23
JC	5-Apr-23
SR	14-Apr-23
AU	14-Apr-23
AS	20-Apr-23
JC	14-Apr-23
JC	14-Apr-23
HL	26-Apr-23
AS	27-Apr-23
SR	27-Apr-23
AS	25-Apr-23
HL	12-May-23
AS	4-May-23
HL	19-May-23
AS	12-May-23
PA	5-May-23
JC	11-May-23
MS	12-May-23
HL	2-Jun-23
AS	19-May-23
SR	11-May-23
JC	17-May-23
AS	19-May-23
MS	19-May-23
SR	24-May-23
MS	2-Jun-23
JC	26-May-23
JC	26-May-23

[VijayP]

@JJP1 Without looking sample data it is difficult to assess the issue

[JJP1]

Hello @VijayP,

On further investigation, I discovered the issue is with my dates table.

Our fiscal year is April to Mar. The dates in January 2024 for some reason are not being accounted for when the 2023-2024 fiscal year filter is applied. For some reason they appear under the 2022-2023 fiscal year data.

This is the fiscal year formula I used in the dates table:

FY =

var _year = if(month([date]) >=4 , year([date]),year([date])-1)

var _month = MONTH([Date])

return

IF(

    _month=1,

    "FY "&(_year-1)&"-"&_year,

    "FY "&_year&"-"&(_year+1)

)

May you please help me identify the error? I am hoping to correct the formula in a way it will not require ongoing maintenance. 

Thanks!

[VijayP]

@JJP1 Check the rounding issues like  - 20.8 will be considered 21 when it is rounded 20.4 will be considered 20 when it is rounded.

the data provided is incomplete since I am not able see the raw data

[JJP1]

Hello @VijayP, 

There are no partial numbers in the data. The table counts the remaining number of dates in a column after a fiscal year filter is applied.