Date table and cumulative values

[Anonymous]

Refering to the screenshot, I am using a date table to display the cumulative values (right matrix) refering to the absolute values (left matrix). So the values hold from week 2023-02 to 2023-05. But when I use the date table to cumulate the weeks from past (i.e., start of date table range) as well as from future (i.e., end of date table range) are appearing which I do not want. I just want it to display the weeks that hold a value (i.e., similar like matrix on the left side). How can I achieve that?

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.  Ensure that in that file, there is a Calendar Table with a week number column.

[v-yiruan-msft · Accepted solution]

Hi @Anonymous ,

You can update the formula of measure as below and check if it can return your expected result...

Your Measure Name =
VAR _mindate =
    CALCULATE ( MIN ( 'Table'[Date] ), ALLSELECTED ( 'Table' ) )
VAR _maxdate =
    CALCULATE ( MAX ( 'Table'[Date] ), ALLSELECTED ( 'Table' ) )
VAR _weeks =
    CALCULATETABLE (
        VALUES ( 'Date'[Calendar Week] ),
        FILTER ( 'Date', 'Date'[Date] >= _mindate && 'Date'[Date] <= _maxdate )
    )
VAR _value = 'your original measure formula'
RETURN
    IF ( SELECTEDVALUE ( 'Date'[Calendar Week] ) IN _weeks, _value, BLANK () )

If the above one can't help you, could you please provide some raw data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples? It would be helpful to find out the solution. You can refer the following links to share the required info:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards