YTD ON selected FiscalPeriod

[Anonymous]

Hi, I am trying to do the below, If say user selects 2022001 & 2022005 in the filter list, I want my YTD to calculate all Amounts below (max(selectedValue(fiscalperiod). I tried DAX to do

Calculate(SUM (amount), Filter(FISCALPERIOD <= MAX(Selected(FiscalPeriod)) but this errors out saying I can't use in this context. 

Any pointers pls.. Thanks Krishna.

FISCALPERIOD	AMOUNT
2022001	100
2022002	100
2022003	100
2022004	100
2022005	100
2022006	100
2022007	100
2022008	100
2022009	100

  FILTERS

2022001
2022002
2022003
2022004
2022005
2022006
2022007
2022008
2022009
2022010

[amitchandak · Accepted solution]

@Anonymous ,

You can use Year and period. But have them in separate table with Key Year Period to join back

YTD= CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year]) && 'Date'[period] <= Max('Date'[period]) ))
LYTD = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year])-1 && 'Date'[period] <= Max('Date'[period])))

Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

[v-jianboli-msft · Accepted solution]

Hi @Anonymous ,

If your tables are like this:

You can use the measure:

YTD =
VAR _max =
    MAXX ( ALLSELECTED ( 'Table'[FISCALPERIOD] ), [FISCALPERIOD] )
RETURN
    CALCULATE (
        SUM ( [AMOUNT] ),
        FILTER ( ALL ( 'Table' ), [FISCALPERIOD] <= _max )
    )

Final output:

If it isn't your expected output, please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

Refer to:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[daXtreme]

Hi @Anonymous 

To do YTD on a Fiscal Period you should also have a Fiscal Year field somewhere... If you don't, then you won't be able to know which Fiscal Year your period is in. Of course, you could do some ugly stunts on values like 2022001 to retrieve the year but this is not recommended and goes against the rules of good PBI/DAX design.

Also, we don't know if the two tables are joined. Depending on this, the DAX will be written differently.

[Anonymous]

Hi @daXtreme , thanks for responding. yes there is Fiscal year too as a field. But what Iam looking for is, like in sql you can filter by say 

select sum(amount) from table where fisacaperiod <= max(selected(fiscalperiod).

Thanks

I am looking ffor something like this  - as it is at aggregated level, it doesn't have a Date field (posting date etc.)

[amitchandak · Accepted solution]

@Anonymous ,

You can use Year and period. But have them in separate table with Key Year Period to join back

YTD= CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year]) && 'Date'[period] <= Max('Date'[period]) ))
LYTD = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year])-1 && 'Date'[period] <= Max('Date'[period])))

Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s