Difficulty making a cumulative total each day

[ChriscarterKor]

Hello everyone,
I hope you are doing very well
Please need your aide🤲🏾
Actually I'm trying to write a formula in dax and I'm a bit stuck

Here is my data model

 

I would like to present data in a table like this on

 

I'm having trouble with the TC MTD column, (i.e. coverage rate by MTD.)
In fact, the fact table (listing) receives the data following the transactions carried out
This table (listing) has a date column [transaction date].
Next to it is a date table that I've created and linked with the (listing) table.

As for the formula I'm looking for,
For each month selected, when I filter on [commercial] or [CDS], it displays the TC by MTD

Which is in fact the cumulative [TC] for each day of the month (obviously starting with the 1st) divided by the number of days to date (selected in the filter).
For example, if I click on June 15, this should give me the cumulative TC from the 1st of the month to the 15th, divided by 15.

And if for the selected date, the sales rep hasn't made any transactions, it should still give me the total since the first day of the month divided by the number of days to date (selected in the filter).

Any help would be appreciated

Thanks

@Greg_Deckler @lbendlin 

[Anonymous · Accepted solution]

Hi,@ChriscarterKor 

Regarding the issue you raised, my solution is as follows:

1.First I have created the following table and the column names and data are the data you have given:

2. Create the following calculation table as a slicer:

Table 2 = VALUES('Table'[Date])

3. Below are the measure I've created for your needs:

MEASURE =
VAR cd =
    DAY ( SELECTEDVALUE ( 'Table 2'[Date] ) )
RETURN
    IF (
        DAY ( MAX ( 'Table'[Date] ) ) <= cd,
        CALCULATE (
            SUM ( 'Table'[values] ),
            FILTER (
                ALLSELECTED ( 'Table' ),
                'Table'[Date] <= MAX ( 'Table'[Date] )
                    && 'Table'[Date].[Month] = MAX ( 'Table'[Date].[Month] )
                    && DAY ( 'Table'[Date] ) <= cd
            )
        ) / cd,
        BLANK ()
    )

4.Here's my final result, which I hope meets your requirements.

Can you share sample data and sample output in tabular format if I am misunderstanding? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

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