Rolling sum (running total) calculated also for future months and cannot be filtered

[Anonymous]

Hi all,

could you please help me with my measure I have for a 12M rolling sum? I got this measure defition on the internet and it computes the numbers fine. But I cannot achieve to change the measure so that I can filter out the months for which I do not have any data, especially those future months that are displayed due to the 12M measure..

Any idea please? Thank you very much

Formula:

OI_12m_roling_m_rate =

IF(
ISFILTERED('Dates'[Date]),
ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
VAR __LAST_DATE = ENDOFMONTH('Dates'[Date].[Date])
VAR __DATE_PERIOD =
DATESBETWEEN(
'Dates'[Date].[Date],
STARTOFMONTH(DATEADD(__LAST_DATE, -11, MONTH)),
ENDOFMONTH(DATEADD(__LAST_DATE, 0, MONTH))
)
RETURN
SUMX(
CALCULATETABLE(
SUMMARIZE(
VALUES('Dates'),
'Dates'[Date].[Year],
'Dates'[Date].[QuarterNo],
'Dates'[Date].[Quarter],
'Dates'[Date].[MonthNo],
'Dates'[Date].[Month]
),
__DATE_PERIOD
),
CALCULATE(
SUM(Data[Actual Net Sales M rate]),
ALL('Dates'[Date].[Day]
)
)
)

[AlB]

Hi @Anonymous 

Try returning blank when the value for the months that don't have data. Something like:

Measure = 
IF( Current Month is not blank, [Your Current Measure ])

 and make sure the chart doe not show blanks. Or you could also check that you are not in the future:

Measure = 
IF( MIN(Date[Date]) <= TODAY(), [Your Current Measure ])

or a combination of both 

[Anonymous]

HI @AlB ,

thank you very much for your quick response. Unfortunately, this did not help, I have already tried that. I also tried different 12M rolling sum measure definitions but have not achieved to get the desired result.

What I would need is 12M rolling sum measure to display in a chart where I could use filters from left or right adn where the chat will not display future months. I also tried the one mentioned below which does not give me 12M sum but only the current month value...

If you have any other tip, I would be really glad as I have already tried various options but still nothing 😕

Thank you very much

orders_invoiced_rolling_sum_12M chart2 = 

CALCULATE (
    SUMX (Data,Data[Actual Net Sales M rate] ),
    DATESBETWEEN (
        Dates[Date],
        NEXTDAY( ( SAMEPERIODLASTYEAR ( LASTDATE ( Dates[Date]) ) )),
        LASTDATE ( Dates[Date] )
    )
)

[AlB]

Can you share the pbix so that I can have a look? 

Can you also explain this a bit more, perhaps with an example? 

What I would need is 12M rolling sum measure to display in a chart where I could use filters from left or right adn where the chat will not display future months.

[Anonymous]

Hi,

thanks. I have already achieved to get rid of the future months (I had some other filters applied on the report level which caused this issue ..my bad..)

Anyway i am still not sure how to avoid those blank months at the beginnign as I cannot use date filter on the chart that contains date hierarchy and a measure relied on it.

Is there a way to attach/post the Pbix file here? I cannot see the option anywhere 🙂 I created an example with just a data sample.

Many thanks!

Johny B

[AlB]

You have to share the URL to the file hosted elsewhere: Dropbox, Onedrive... or just upload the file to a site like tinyupload.com (no sign-up required).

[Anonymous]

Hi,

thank you for your reply. You can find the example here:

http://s000.tinyupload.com/?file_id=25466564158205816040

Thank you very much for any advice!

Johny