cumulative sales filters

JC2022 — Mon, 27 Mar 2023 18:53:34 GMT

Hi,

My cumulative sales formula is working (see formula below), but the filter on [CurFiscalYearOffset] seems to be not working. My cumulative sales line is now summing for all completed months as it should be. But it is only working if I put a filter on the visual with [CurFiscalYearOffset] = 0. So not via this formula below.

Further, my cumulative sales line is going all the way horizontal for all unfinished months with the same value as the last finished month, but ideally the line should stop at the last finished month.

Can you guys help me with these 2 issues?

Cumulative EUR Sales = CALCULATE(SUM(Append3[EUR Sales]),filter(ALL('Calendar Posting Date'),'Calendar Posting Date'[Date] <=max('Calendar Posting Date'[Date]) && 'Calendar Posting Date'[CurMonthOffset] < 0 && 'Calendar Posting Date'[CurFiscalYearOffset] = 0))

Re: cumulative sales filters

amitchandak — Tue, 28 Mar 2023 02:29:36 GMT

JC2022 , this should work, if use period(if needed ) from 'Calendar Posting Date' in the visual

Cumulative EUR Sales = CALCULATE(SUM(Append3[EUR Sales]),filter(ALL('Calendar Posting Date'),'Calendar Posting Date'[Date] <=max('Calendar Posting Date'[Date]) )

if want stop till current month try like

if(Max('Calendar Posting Date') <= Today(), [Cumulative EUR Sales], blank())

You can also consider new window function

Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc

YouTube Podcast of all my videos (10+) on Offset, Window, and Index functions. Hope you will like that: https://www.youtube.com/watch?v=GpoITi_tRIw&list=PLPaNVDMhUXGax-j-mEF8ZxL5eV-VcmdvJ&index=5

topic cumulative sales filters in DAX Commands and Tips

cumulative sales filters

Re: cumulative sales filters