!2 month rolling filter with 0's between the 12 months

[GlynMThomas]

I have a measure attached to a orphaned date table which allows my filter to display a 12 month period based on the date selected. This works fine, however any dates within the selected period that have no transactions don't show at all in my tables. I would like these to show as zero but obviously not show any dates outside this 12 month period. Every change I've tried so far doesn't work, it just pulls back all the other months I don't want to display. Is there a simple way I can adjust the measure below to do this?

Application Filter =

VAR CurrentDate = MAX('Filter Dates'[Date])

VAR PreviousDate = DATE(YEAR(CurrentDate), MONTH(CurrentDate) -11, DAY(CurrentDate) - DAY(CurrentDate) + 1)

VAR Result =

CALCULATE(

SUM(Applications[Total Application]),

FILTER(

Dates,

Dates[Date] >= PreviousDate && Dates[Date] <= CurrentDate

)

)

RETURN Result

[amitchandak]

@GlynMThomas , try to return To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

RETURN Result+0 and check.

[az38]

Hi @GlynMThomas 

to show zeroes you need to create a calendar table like

CALENDARAUTO()

create relationships between calendar and your fact tables and put calendar table dates as X-axis into your visual

[GlynMThomas]

I already have a calendar table setup and tried to pull a zero across to fill in the gaps but it doesn't work.

Effectively I want the table to show like this:

Month	Amount
Jul-20	200
Jun-20	0
May-20	100
Apr-20	200
Mar-20	0
Feb-20	0
Jan-20	0
Dec-19	400
Nov-19	0
Oct-19	300
Sep-19	200
Aug-19	100

But at the moment I get this:

Month	Amount
Jul-20	200
May-20	100
Apr-20	200
Dec-19	400
Oct-19	300
Sep-19	200
Aug-19	100

So missing the table with zeros as they come through as blank because there aren't any transactions those months. The above shows when the filter is set to the most recent date.

[az38]

@GlynMThomas 

if you use the Calendar table as a Month column, it should be enough to set parameter Show items with no data as active for [Amount] column

[GlynMThomas]

Unfortunately that then shows items outside of the measures date range as well which I don't want to see. At the moment the measure is setting anything outside the 12 month period as blank so it doesn't show, but this also means that anything inside that range that is blank as doesn't show. It's these values that I need to set to zero.

[az38]

@GlynMThomas 

change your measure to

Application Filter =
VAR CurrentDate = MAX('Filter Dates'[Date])
VAR PreviousDate = DATE(YEAR(CurrentDate), MONTH(CurrentDate) -11, DAY(CurrentDate) - DAY(CurrentDate) + 1)
VAR Result =
CALCULATE(
SUM(Applications[Total Application]),
FILTER(
Dates,
Dates[Date] >= PreviousDate && Dates[Date] <= CurrentDate
)
)
RETURN 
IF(ISBLANK(Result), 0, Result)

and you will see zeros

[GlynMThomas]

Thanks,

I've tried that though, it does bring zeros in but also then gives me everything outside the 12 month range which I don't want. The measure is basically filtering out everything that doesn't fall between 10/07/2020 and 10/07/2019. So when you select 10/07/2020 on the filter you get a table showing the amounts. It's quite fiddly as the behaviour of the measure causes issues.

[v-shex-msft]

HI @GlynMThomas,

Please provide some dummy data with expected results then we can test to coding format on it based on your requirements.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[GlynMThomas]

Sure, here is a power bi file with the measure and table built.

https://drive.google.com/file/d/1SCJKXrPOxXQrW0LBF6UWWhgvCUnPf79E/view?usp=sharing

I'm just trying to show 12 months over the period selected by the filter and measure, so if there are no transactions in a month it should show as zero, however it should not show any months outside of the 12 month period.

[v-shex-msft]

Hi @GlynMThomas,

I can't view the shared link, it still required some additional permissions to access it, can you fix this?

Regards,

Xiaoxin Sheng

[GlynMThomas]

Sorry try this:

https://drive.google.com/file/d/1SCJKXrPOxXQrW0LBF6UWWhgvCUnPf79E/view?usp=sharing

[v-shex-msft · Accepted solution]

HI @GlynMThomas,

You can use the following measure formula to achieve your requirement:

Measure = 
VAR CurrentDate =
    MAX ( 'Transactions'[Date] )
VAR PreviousDate =
    DATE ( YEAR ( CurrentDate ), MONTH ( CurrentDate ) - 11, 1 )
VAR range =
    CALENDAR ( PreviousDate, CurrentDate )
VAR Result =
    CALCULATE (
        SUM ( Transactions[Amount] ) + 0,
        FILTER (
            ALL ( Transactions ),
            Transactions[Date] IN range
                && Transactions[Date] <= MAX ( 'Calendar'[Date] )
        )
    )
RETURN
    IF ( MAX ( 'Calendar'[Date] ) IN range, Result )

Visual design:
1. Use transaction table date field as source of a slicer.
2. Create a table visual with calendar date, measure without aggregation modes.

Regards,

Xiaoxin Sheng