Data values shown for rows which have no data by measure

[aforapple_27]

 
Here is the table snapshot. Basically I created a cumulative measure, but the problem is that if there is no data, for example here we have no data after Month 4, then nothing should be shown in under FY25(running) measure. but before the max date of the data, even if there is no data FY25 column needs to be populated with 0. 

Please let me know if further information is needed. This issue has been bugging me for a while, and any help with this would be greatly appreciated

[Anonymous]

Hi @aforapple_27 ,

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[aforapple_27]

Creating a dummy data set will take some time as the data set is verey complex. But here is one interesting thing that has been happening here. Here is the dax code. 

FY 24 =
var debuggingVariable = Calculate(MAX('DateTable'[Date]),FILTER(All('Raw data'),

            'Raw data'[Month] = MAX('Raw data'[Month]) &&

            'Raw data'[Year] = SELECTEDVALUE('Raw data'[Year]) -1))
Return debuggingVariable

as you can see in above picture, even though there is some transaction done in July 2023, above code is not evaluating any date for the transaction (max date of transaction). The thing is there was no transaction done in july 2024. I can't understand why this DAX query is not returning any data. (Note: Dax query Max('raw data'[Date]') will evaluate for year 2024, as it is the selected year in dashboard.)

Please let me know if further information is needed or dummy pbix will ultimately be needed for the support.

[Anonymous]

Hi @aforapple_27,

I'd like to suggest you create a unconnected date table and use it as date axis of table visual to looping raw table records:

Existed PrevDate =
VAR selection =
    MAX ( NewTable[Date] )
VAR prevRawDate =
    CALCULATE (
        MAX ( 'Raw data'[Date] ),
        FILTER (
            ALLSELECTED ( 'Raw data' ),
            'Raw data'[Month] = MONTH ( selection )
                && 'Raw data'[Year]
                    = YEAR ( selection ) - 1
        )
    )
RETURN
    prevRawDate

Regards,

Xiaoxin Sheng

[aforapple_27]

Here is a dummy data set that I have. Can you try replicating my power BI table. ( Note: The month and year column in the dataset refer to fiscal year and month. No need to include month name. ) Sell_Data_Dummy_Filled.xlsx 

[Ashish_Mathur]

Hi,

In another tab of the Excel, show the expected result in a table format.

[aforapple_27]

Hi everyone,

This is the expected visual I am looking for. This is data shown when I select fiscal year 2025. I would be adding slicers for fiscal year and Categories. Let me know if any questions.

[bhanu_gautam]

@aforapple_27 , To handle missing data you can create a measure like

FY25 =
VAR MaxDate = [MaxDate]
RETURN
IF(
MAX('DateTable'[Date]) <= MaxDate,
COALESCE(SUM('YourTable'[Value]), 0),
BLANK()
)