Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have a fact table like this:
Column | Description |
DATE | Our normal date |
DATE_LAST_YEAR |
Specific date from last year, based on this calculation: date(if(year(DATE)-(round(year(DATE)/4)*4) = 0, num(DATE) - 371, NUM(DATE)-364)) |
KPI | Our KPI |
Now I want to make a table or chart, which shows me my KPI for each Date and its date_last_year. But it should be in the same row. I try to show my needs with this screenshot:
I don´t know, how to make my measure, to show the KPI from last (specific date) in the right row.
Maybe someone has an idea?
Thanks a lot.
Also tried this one and does not work: Sum({$<DATE={$(=DATE_LAST_YEAR)}>}KPI)
please post some sample data in excel,
Yes of course.
Here it is.
Main:
LOAD
"DATE",
DATE_LAST_YEAR,
KPI
FROM [lib://AttachedFiles/LAST_YEAR_SAMPLE.xlsx]
(ooxml, embedded labels, table is Sheet1);
Left Join(Main)
LOAD
"DATE" as DATE_LAST_YEAR,
KPI as last_year_KPI
FROM [lib://AttachedFiles/LAST_YEAR_SAMPLE.xlsx]
(ooxml, embedded labels, table is Sheet1);
exit script;
In Chart
Thank you.
I forgot to mention, that I already know this solution. It will be my solution, if I won´t find another in the next days.
I am looking for something like Set Analysis or Aggr, because I don´t want to double my KPIs in the data model.
There is no front end only solution for this
Please create day& Month dimension in calendar then apply year in the set analysis: ie
Date CY LY
1-May sum({<Year={'2022'}>}qty) sum({<Year={'2021'}>}qty)
Thanks