Forum Discussion
Helper ICumulative Total , no dates
UrAvgWally Yeah, there is a stray ) in there:
Measure = VAR PNo = MAX(PeriodicTable[PeriodNo]) VAR Periods = SELECTCOLUMNS( FILTER( ALL(PeriodicTable), [PeriodNo] <= PNo ), "__PeriodMFMA", PeriodicTable[PeriodMFMA] ) VAR Table = FILTER(UnpivotCP,[PeriodMFMA] IN Periods) RETURN SUMX( Table,[Value])
Helper IGreg_Deckler Hiya! Still not working 😞 .
Community ChampionUrAvgWally Yeah, there is a stray ) in there:
Measure =
VAR PNo = MAX(PeriodicTable[PeriodNo])
VAR Periods =
SELECTCOLUMNS(
FILTER(
ALL(PeriodicTable),
[PeriodNo] <= PNo
),
"__PeriodMFMA",
PeriodicTable[PeriodMFMA]
)
VAR Table = FILTER(UnpivotCP,[PeriodMFMA] IN Periods)
RETURN
SUMX( Table,[Value])- Greg_Deckler
UrAvgWally You will want to use ALLEXCEPT instead of ALL in that case. Basically ignore all of the context except for the context you specify.
- UrAvgWally
Hi Greg_Deckler ,
The syntax error has been fixed, it gave an error saying Table is reserved so changed it to Table1, not sure if that is the right thing to do? But to add to that, it is still showing me on the graph, the sum by period rather than cumulative Sum in the graph not sure if it because I changed to Table1?.
- Greg_Deckler
UrAvgWally Right, one of the reasons I prefix my variables with __ to avoid conflicts like that. Tough to troubleshoot in forums without access to the PBIX or sample data to build the model myself. The theory behind the forumla is that you get the current period as variable Pno so PeriodicTable[PeriodNo] should be used in the X-Axis. You then use that to get all periods equal or less than that period. You can use CONCATENATEX to view the items in that table. Then you filter your unrelated table UnpivotCP for only the rows where the period is in your list of current and previous periods (Periods variable). Perhaps you have a relationship that is messing this up? If so, you could use ALL to get around this like:
VAR Table1 = FILTER(ALL(UnpivotCP),[PeriodMFMA] IN Periods) - UrAvgWally
Greg_Deckler Awesome! The code fixed the issue and is now showing cumulative.
Although my slicer for different functions not working for that.I ideally want to be able to Slice by function , is there a way to enable that? As per above I use RSKCL[Function] as a slicer, which has a relation to the Funder column.
- UrAvgWally
Alright, thanks . Thank you for being patient with me. Will start reading the book on Dax over the weekend to learn, it is arriving tommorow.
The Allexcept is having syntax error :
- Greg_Deckler
UrAvgWally ALLEXCEPT takes additional parameters. So something like:
VAR Table1 = FILTER(ALLEXCEPT(UnpivotCP, 'UnpivotCP'[Function]),[PeriodMFMA] IN Periods)So that code would ignore all context other than context created by a Function column in your UnpivotCP table for example.