Date table not providing all the data

[abihoughton27]

I have created a dates table so i can have formatting (I.e for FY start etc) but for some reason the measure is not working fully for the date table

the table on the left is using the month from the date table and the table on the right is using the date from the table the measure is using

I have created a measure which has userelationship

RT =

calculate(

[Baseline FTE Sum],'FTE Milestone Benefits'[Current realisation] <= MAX('FTE Milestone Benefits'[Current realisation]),

ALLSELECTED('FTE Milestone Benefits'[Current realisation]),

USERELATIONSHIP(

'FTE Milestone Benefits'[Current realisation],

'Date Table'[Date]

)

)

and this is the relationship

why is it not pulling through all the data for the dates?

[v-jingzhang · Accepted solution]

Hi @abihoughton27 

I see you have two years data in tables. If you drag only Month column into the visual, data in the same month of different years will be aggregated together and makes the running total wrong.

To fix it, you could add a Year-Month column into Date table and use it in the matrix visual. Or add Year into the matrix as Rows field too. Or if you only want to get running total in a selected year, you can add a Year filter or a Year slicer to select a specific year. Then try the following measure:

RT =
CALCULATE (
    [Baseline FTE Sum],
    FILTER (
        ALL ( 'Date Table'[Date] ),
        'Date Table'[Date] <= MAX ( 'Date Table'[Date] )
    ),
    USERELATIONSHIP ( 'FTE Milestone Benefits'[Current realisation], 'Date Table'[Date] )
)

This article should be helpful: Running Total in Power BI - Goodly. It calculates running total across all months. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[amitchandak]

@abihoughton27 , try a measure like

RT =
calculate(calculate(
[Baseline FTE Sum], USERELATIONSHIP(
'FTE Milestone Benefits'[Current realisation],
'Date Table'[Date]
)),filter(ALLSELECTED('Date Table'[Date]) , 'Date Table'[Date] <= MAX('Date Table'[Date]) ))

[abihoughton27]

@amitchandak unfortunatley that just gives me the total and not the breakdown of the running total