Cumulative calculation does not show months/weeks without imput

[milson4]

Situation:
I have a data table:

Reports, containing issued date, report no. etc.,,.

DateTable contains all dates from beginning of the year till end of the year.

Problem: Calculation:
IssuedReportsCumulated = Calculate ([IssuedReports], ALL ('DateTable'),

FILTER(ALL('DateTable'),

'DateTable'[IssuedDate] <= MAX (DateTable)[IssuedDate])

Note: IssuedReport is a measure which counts reports

This calculation works only for months/weeks where at least 1 report has been issued, if not, there's a empty space in the column chart. I would need to take the value from previous day/week or month as a value for a week where there was no report issued.

Thank you all for support

[v-priyankata · Accepted solution]

Hi @milson4 

Thank you for reaching out to Microsoft Fabric Community.

please try below DAX it may helps you.

IssuedReportsCumulated = 
VAR MaxDate = MAX('DateTable'[IssuedDate])
VAR LastReportDate = 
    CALCULATE(
        MAX('Reports'[IssuedDate]),
        'Reports'[IssuedDate] <= MaxDate,
        ALL('DateTable')
    )
RETURN
CALCULATE(
    [IssuedReports],
    FILTER(
        ALL('DateTable'),
        'DateTable'[IssuedDate] <= LastReportDate
    )
)

If this information is helpful, please “Accept as solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.

[v-priyankata · Accepted solution]

Hi @milson4 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-priyankata · Accepted solution]

Hi @milson4 

Thank you for reaching out to Microsoft Fabric Community.

please try below DAX it may helps you.

IssuedReportsCumulated = 
VAR MaxDate = MAX('DateTable'[IssuedDate])
VAR LastReportDate = 
    CALCULATE(
        MAX('Reports'[IssuedDate]),
        'Reports'[IssuedDate] <= MaxDate,
        ALL('DateTable')
    )
RETURN
CALCULATE(
    [IssuedReports],
    FILTER(
        ALL('DateTable'),
        'DateTable'[IssuedDate] <= LastReportDate
    )
)

If this information is helpful, please “Accept as solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.

[rajendraongole1]

Hi @milson4  - you can modify the cumulative Issued Reports as below:

IssuedReportsCumulatedFill =
VAR CurrentDate = MAX('DateTable'[Date])
RETURN
CALCULATE(
SUMX(
FILTER(
ALL('DateTable'),
'DateTable'[Date] <= CurrentDate
),
[IssuedReports]
)
)

If [IssuedReports] is a simple count of reports per day, this will sum them over all dates up to the current one- including days where count is 0

Hope this works. 

[milson4]

Hello @rajendraongole1 

Thank you very much for your support.

Unfortunatelly modified code didn't help in my case. I can still see only months/weeks in which at least 1 report has been issued.