Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Icon for Administrator rankAdministrator
4 years ago

Show boxes grouped by selected year

I need to show a graph of bars grouped by year, it must be the year selected in the filter and the year before the one selected in the filter.

How can I do this?

I have tried to place the selected year and the previous year in measures and then in other measures add everything that coincides but does NOT work for me.

CURRENT SEL YEAR = FORMAT(MAX(HISTORY[MATRICULA_FECHA]),"yyyy")
PREVIOUS SEL YEAR = FORMAT(EOMONTH(MAX(HISTORY[MATRICULA_FECHA]),-12),"yyyy")
CURRENT SUM YEAR =
where curr_year= [CURRENT SEL YEAR] return
CALCULATE(
SUM(HISTORICAL[REGISTERED]),
FILTER(
HISTORICAL,
FORMAT(HISTORY[MATRICULA_FECHA], "yyyy")=curr_year
)
)
PREVIOUS YEAR SUM =
where prev_year = [PREVIOUS SEL YEAR] return
CALCULATE(
SUM(HISTORICAL[REGISTERED]),
FILTER(
HISTORICAL,
FORMAT(HISTORY[MATRICULA_FECHA], "yyyy")=prev_year
)
)

Something like this should be the PBIx:

Filter: YEAR

Graph: Bars grouped by Year selected and Year prior to the selected

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    Syndicate_Admin , You should always use date table, marked as date table and then you can try measures like

     

    example measures

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

     

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

     

    //Only year vs Year, not a level below

    This Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
    Last Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))
    diff = [This Year]-[Last Year ]
    diff % = divide([This Year]-[Last Year ],[Last Year ])

     

     

    Power BI — Year on Year with or Without Time Intelligence
    https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
    https://www.youtube.com/watch?v=km41KfM_0uA