Previous YTD with no date column

[Anonymous]

Hello,

I am trying to dd two measures to add running totals to this data, one by programme_period YTD and one for the previous ytd.

I have managed the YTD no problem but cannot seem to get the pytd working. I have tried adding year - 1 as a variable, within the filter context (using Max(year) -1) and various other combinations. 

If for example I am presenting this data in a table visual I would like to see the following 

Programme Period	YTD	PYTD
2001	875,205	451,941

Any help greatly appreciated!

Thanks,

Kevin

[camargos88 · Accepted solution]

Hi @Anonymous ,

Try this code:

pYTD =

VAR _p_YEAR = SELECTEDVALUE('Table (3)'[Programme Year]) - 1

VAR _p_MONTH = INT(RIGHT(_p_YEAR; 2) & RIGHT(SELECTEDVALUE('Table (3)'[Programme Period]); 2))

RETURN

CALCULATE(SUM('Table (3)'[Targeted]); FILTER(ALL('Table (3)'); 'Table (3)'[Programme Year] = _p_YEAR && 'Table (3)'[Programme Period] <= _p_MONTH))

Ricardo

[Anonymous]

Hi @camargos88 

Yes this works!

Any idea why these values don't work on cards? 

Kevin

[v-shex-msft]

HI @Anonymous,

Measures are calculated based on current row content and correspond category groups. Its row content equal to the whole table If you use it in the card. when you use selectedvalue function in this scenario, it will return multiple values.  (Your formula seems not to add option expressions to deal with multiple rows so it will return default value blank and cause the wrong filter result)
Regards,

Xiaoxin Sheng

[Greg_Deckler]

Perhaps:

PYTD =

  VAR __Year = MAX('Table'[Year])

RETURN

  SUMX(FILTER(ALL('Table'),[Year] = __Year-1),[Some column])

See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

[amitchandak]

First of all, create a year dimension and then try like

Prior Year= calculated([Measure],filter(All(Year),Year[Year]<=max(Year[Year])-1))

If you have a date, you can use with date dimension

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))
This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR('Date'[Date])),"12/31"))

Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

[Anonymous]

Hi @amitchandak,

Thanks for your response. I don't have a Date Table, I'm hoping to figure out a way to do so with out one.