Forum Discussion

abihoughton27's avatar
abihoughton27
Icon for Helper I rankHelper I
4 years ago
Solved

Date table not providing all the data

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?

 

Need Help
Tips and Tricks
Tutorial Requests
  • v-jingzhang's avatar
    v-jingzhang
    4 years ago

    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.

3 Replies

  • v-jingzhang's avatar
    v-jingzhang
    Icon for Community Support rankCommunity Support
    4 years ago

    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's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    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]) ))