Trouble with DAX to calculate YTD by Quarter with specific rules

[Laveezai]

My problem is as follows:

I have facts table containing Actual YTDs entered at each quarter.

This table is composed of the following fields:

Actual YTD (int) | Date (DateTime of creation) | Project Code (Text) | Quarter (Text -> Q1 2024, etc.)

I'd like to sum up the values for each quarter, but the problem is that within a quarter, a project doesn't necessarily have a new Actual YTD, and each Actual YTD entered by Quarter represents the cumulative total of the previous Quarter and the current Quarter.

However, I'd like to retrieve the most recent Actual YTD before that quarter, if it doesn't have an Actual YTD.

I'm having trouble thinking about creating a measure to solve this problem. Could you help me?

Thanks in advance

[v-nuoc-msft]

Hi @Laveezai 

I found your post while I was checking the forums, has your problem been solved? If not, here is the method I provided:

Here's some dummy data

"Table"

Create measures.

MarkNum = 
VAR _MaxQuarter = CALCULATE(MAX('Table'[Quarter]), ALL('Table'))
var _codeMaxQuarter = CALCULATE(MAX('Table'[Quarter]), FILTER(ALL('Table'), 'Table'[Project Code] = MAX('Table'[Project Code])))
RETURN
IF(
    _codeMaxQuarter = _MaxQuarter, 
    SELECTEDVALUE('Table'[Project Code]), 
    0
)

Sum Actual YTD = 
VAR _MaxQuarter = CALCULATE(MAX('Table'[Quarter]), ALL('Table'))
var _CodeMaxQuarter = CALCULATE(MAX('Table'[Quarter]), FILTER(ALL('Table'), 'Table'[Project Code] = MAX('Table'[Project Code]) && 'Table'[Quarter] <> _MaxQuarter))
RETURN

IF(
    'Table'[MarkNum] <> 0,
    CALCULATE(
        SUM('Table'[Actual YTD]), 
        FILTER(
            ALL('Table'), 
            'Table'[Quarter] <= MAX('Table'[Quarter]) 
            && 
            'Table'[Project Code] = MAX('Table'[Project Code]) 
        )
    ),
    CALCULATE(
        SUM('Table'[Actual YTD]),
        FILTER(
            ALL('Table'),
            'Table'[Actual YTD] = MAX('Table'[Actual YTD])
            &&
            'Table'[Quarter] = _CodeMaxQuarter
        )
    )
)

Here is the result.

Regards,

Nono Chen

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

[Ritaf1983]

Hi @Laveezai 

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523