Filters does not work when +0 is added to remove blanks

[Anonymous]

Hello, 

I need to visualize the last 12 months' data from a selected date, for that, I have created a date table(DTEntryDateTable) that is not connected with the fact table and used this measure to visualize 12-month data in the bar chart. 

Measure=
VAR CurrentDate = MAX(DTEntryDateTable[dteCreatedOn])
VAR PreviosData = DATE(YEAR(CurrentDate),MONTH(CurrentDate)-12,DAY(CurrentDate))
VAR Result = 

CALCULATE(
    COUNT(tblIndexDT[numIndexID]),
    
    FILTER(
        tblIndexDT,
        tblIndexDT[dteCreatedOn]>=PreviosData && tblIndexDT[dteCreatedOn]<=CurrentDate
    )
)
RETURN
Result

This measure works fine and it does the job, But I need to show blank values as "0", Therefore I changed the measure into this,

Measure=
VAR CurrentDate = MAX(DTEntryDateTable[dteCreatedOn])
VAR PreviosData = DATE(YEAR(CurrentDate),MONTH(CurrentDate)-12,DAY(CurrentDate))
VAR Result = 

CALCULATE(
    COUNT(tblIndexDT[numIndexID])+0,
    
    FILTER(
        tblIndexDT,
        tblIndexDT[dteCreatedOn]>=PreviosData && tblIndexDT[dteCreatedOn]<=CurrentDate
    )
)
RETURN
Result

But when I add +0 for the count, it ignores the filter in Dax and shows data for every month which is available in the dataset with zeros for blank values.

Please help me to figure out a way to show zeros for blank values without losing the filters in DAX.

Thank you.

[Pragati11 · Accepted solution]

Hi @Anonymous ,

Can you try the following DAX:

Measure=
VAR CurrentDate = MAX(DTEntryDateTable[dteCreatedOn])
VAR PreviosData = DATE(YEAR(CurrentDate),MONTH(CurrentDate)-12,DAY(CurrentDate))
VAR Result = 
CALCULATE(
    COUNT(tblIndexDT[numIndexID]),
    FILTER(
        tblIndexDT,
        tblIndexDT[dteCreatedOn]>=PreviosData && tblIndexDT[dteCreatedOn]<=CurrentDate
    )
)
RETURN
IF(ISBLANK(Result), 0, Result)

Thanks,

Pragati

[aggisss]

Hi,
have you solced this issue?
I have similar problem,
even if I add filter on a visual for offset month is not working.
Looks like function is blank, removes date filter.

[v-janeyg-msft]

Hi, @Anonymous 

According to your description, Your formula is correct, but there is a problem with the result, So can you share some sample fake data and your visual result like? We need to judge the context.

Best Regards

Janey Guo

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

[Anonymous]

Hello Janey Guo,

Thank you for the response.😊

This is the output from the current measure,

Here I want to display zero for blanks

Details of above-mentioned matrix,

1. Row - Statuses from a dimension table
2. Column - A calculated column of months in YYYY-MM format
3. Measure - Above mentioned measure to display last 12 months data.

Below is the data model,

Relationships,

1. Status code relationship between tblIndexHD and CFIndexstatus
2. Date relationship with date table (for the measure).

Thank you very much for your valuable time. 

[Pragati11]

Hi @Anonymous ,

Try chnaging your measure to as follows:

Measure=
VAR CurrentDate = MAX(DTEntryDateTable[dteCreatedOn])
VAR PreviosData = DATE(YEAR(CurrentDate),MONTH(CurrentDate)-12,DAY(CurrentDate))
VAR Result = 

CALCULATE(
    COUNT(tblIndexDT[numIndexID]),
    
    FILTER(
        tblIndexDT,
        tblIndexDT[dteCreatedOn]>=PreviosData && tblIndexDT[dteCreatedOn]<=CurrentDate
    )
)
RETURN
Result + 0

Thanks,

Pragati 

[Anonymous]

Thank you @Pragati11  for your response,

But still, it does not work.

☹️

[Pragati11]

Hi @Anonymous ,

I guess then you will have to add more details on your data and calculations.

Thaks,

Pragati

[Anonymous]

Hello Pragathi,

Thank you for the response.😊

This is the output from the current measure,

Here I want to display zero for blanks

Details of above-mentioned matrix,

1. Row - Statuses from a dimension table
2. Column - A calculated column of months in YYYY-MM format
3. Measure - Above mentioned measure to display last 12 months data.

Below is the data model,

Relationships,

1. Status code relationship between tblIndexHD and CFIndexstatus
2. Date relationship with date table (for the measure).

Thank you very much for your valuable time. 

[Pragati11 · Accepted solution]

Hi @Anonymous ,

Can you try the following DAX:

Measure=
VAR CurrentDate = MAX(DTEntryDateTable[dteCreatedOn])
VAR PreviosData = DATE(YEAR(CurrentDate),MONTH(CurrentDate)-12,DAY(CurrentDate))
VAR Result = 
CALCULATE(
    COUNT(tblIndexDT[numIndexID]),
    FILTER(
        tblIndexDT,
        tblIndexDT[dteCreatedOn]>=PreviosData && tblIndexDT[dteCreatedOn]<=CurrentDate
    )
)
RETURN
IF(ISBLANK(Result), 0, Result)

Thanks,

Pragati

[Anonymous]

Thank you for the response @Pragati11 ,

But still, the problem is there, below is the faulty output,

 I want to show data only for the last twelve months(March 2021 to March 2020) with zero for blanks, but here it shows data for every month.

[v-janeyg-msft · Accepted solution]

Hi, @Anonymous 

The data that was hidden by the blank value is now displayed as 0. You can add a date slicer to filter data from March 2023 to March 2021 or use filter pane to filter date column. 

Best Regards

Janey Guo

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