Return a sum of a value from a table by a date

[Anonymous]

Hello, I am currently using this DAX formula to return the date with the largest amount of sum hours.

Peak Earned Date = CALCULATE(max(fact_ManhourProgress[Date]),TOPN(1,allselected(fact_ManhourProgress[Date]),[Earned Total] ,DESC),VALUES(fact_ManhourProgress[Date]))

I would then like to take that date and retrieve a sum of a value from another table, but I don't think it is working because I do not think the first formula is only returning one date, but a list. Is that correct?

Here is the formula I am attempting to use to get the other data

Peak Earned Headcount = CALCULATE(SUM(fact_TrackActuals[Heacount]),FILTER(dim_Date,dim_Date[Date]=(CALCULATE([Earned Total],TOPN(1,allselected(fact_ManhourProgress[Date]),[Earned Total] ,DESC),VALUES(fact_ManhourProgress[Date])))))

[Anonymous]

Hi @Anonymous ,

As @danextian  said, providing detailed data (without sensitive information) combined with the results you want to get enables us to better understand and solve the problems you are experiencing.

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

Best Regards,

Ada Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[danextian]

Hi @Anonymous ,

Without some sample data to work on it's hard to suggest a better solution. With the current available information, you can just create  a measure to get the top date.  Try either of these:

TOP DATE =
MAXX (
    TOPN (
        1,
        ADDCOLUMNS (
            SUMMARIZE ( fact_ManhourProgress, fact_ManhourProgress[Date] ),
            "@Total", [Earned Total]
        ),
        [@Total], DESC
    ),
    [Date]
)

TOP DATE =
MAXX (
    TOPN (
        1,
        ADDCOLUMNS (
            SUMMARIZE ( ALL( fact_ManhourProgress ), fact_ManhourProgress[Date] ),
            "@Total", [Earned Total]
        ),
        [@Total], DESC
    ),
    [Date]
)

A better approach would be to use a separate dates table for all your time intelligence calculations.

TOP DATE =
MAXX (
    TOPN (
        1,
        ADDCOLUMNS ( SUMMARIZE ( Dates, Dates[Date] ), "@Total", [Earned Total] ),
        [@Total], DESC
    ),
    [Date]
)

[Anonymous]

Okay I do have a date table. I used your third example, but it isn't working as expected. I am expecting to be able to retrieve the date where the hours are the highest, and then be able to take that calculation to retrieve a sum of the headcounts for that date from another table. Below is the example

Peak Hours Date = 
MAXX (
    TOPN (
        1,
        ADDCOLUMNS ( SUMMARIZE ( dim_Date, dim_Date[Date] ), "@Total", [Earned Total] ),
        [@Total], DESC
    ),
    [Date]
)

Peak Headcount = CALCULATE(SUM(fact_TrackActuals[Heacount]),FILTER(dim_Date,dim_Date[Date]=[Peak Hours Date]))

[danextian]

Can you please post a sanitized and workable sample data (not an image).