Having past, current and future value in a column

[nazrinf]

Hello,

I have a case whereby I need to display the value of past years, current year and future years.

I have two fact tables which one table contains historical data and current year, while the other table contain future data. 

Referring to the table, my past years data should be taken from ACTUAL measure,
CURRENT_VALUE have calculation:
(ACTUAL_TARGET * SPLY ACTUAL )/ PREVIOUS YEAR ACTUAL

For FUTURE data: RECURRING_YEAR - FOLLOWING_YEAR

(eg: 2023: 220930 - 42279 = 178651, 2024: 178651 - 11494 = 167157)

This is my calculation:

TOTAL_ =

IF(

TODAY() < MAX('DIM_MONTH'[YEAR]),

KPI_MEASURE[CURR_VALUE],

(IF(

TODAY() = MAX(DIM_MONTH[YEAR]),

[GLIDE_CURRYR],

(IF(

TODAY() > MAX(DIM_MONTH[YEAR]), [NEXT_YR],0))))

)

Thank you.

[nazrinf]

Hi @v-chenwuz-msft ,

Thank you for your suggestion workaround. Previously I uploaded wrong data source. Herewith I attached along the data source and the pbix file. I'm still new with DAX calculation and have difficulty to create the measure.

I have problem to get the value for Same Month Last Year value as per screenshot below. I need to get the right value for SPLY before I could proceed to calculate the current year which as per my previous explaination:

I need to create a glidepath analytics graph basically is stacked bar graph with YEAR as shared x-axis, and Region as my legend. In this graph, contains past years, current year and future year data. For past years, the data is coming from FACT_KPI and the future data is coming from FACT_MENUCARD. The fiscal year start Dec 2021 to Nov 2022.

For current year there is the calculation for it. For past months (Dec 2021 – June 2022), the data is from FACT_KPI. As for current month (July 2022) and the remaining months (August – November 2022), the calculation is current month target * actual same month last year / actual previous year.

For future year, the calculation is recurring year – following year.

My question is how to calculate SPLY, current year and previous year in fiscal year based on my data source. I really need some help on this.

Here is the data source: https://docs.google.com/spreadsheets/d/1gZZGuneL7RY3zen4uxcfTATMMcEckorp/edit?usp=sharing&ouid=11075...

Here is the pbix: https://drive.google.com/file/d/11QcfVGSZ8OULP7o_aBEXpP0DTbOvY13-/view?usp=sharing

Thank you in advance.

[lbendlin]

You need a proper Calendar table with contiguous dates to use the Time Intelligence functions like SAMEPERIODLASTYEAR. Your DIM_MONTH table is not adequate.

[lbendlin]

Please provide sanitized sample data that fully covers your issue. I cannot help you without usable sample data.
Please paste the data into a table in your post or use one of the file services like OneDrive or Google Drive. I cannot use screenshots of your source data.
Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[nazrinf]

Hi,

Herewith is the sample file source for my case study. It has 3 worksheets, DIM_MONTH, FACT_KPI and FACT_MENUCARD.

I need to create a glidepath analytics graph basically is stacked bar graph with YEAR as shared x-axis, and Region as my legend. In this graph, contains past years, current year and future year data. For past years, the data is coming from FACT_KPI and the future data is coming from FACT_MENUCARD. The fiscal year start Dec 2021 to Nov 2022.

For current year there is the calculation for it. For past months (Dec 2021 – June 2022), the data is from FACT_KPI. As for current month (July 2022) and the remaining months (August – November 2022), the calculation is current month target * actual same month last year / actual previous year.

For future year, the calculation is recurring year – following year.

My problem is I don’t have date column in my dim_month table. I have problem to create same month last year calculation, current year and future year.

https://drive.google.com/file/d/1B8_sw2mvlbqV-tszLUGLTLRuBbRIXXIr/view?usp=drivesdk

My expected outcome would be like this:

Thank you.

[v-chenwuz-msft]

Hi @nazrinf ,

You can create a custom column in power query editor via this code:

Then you willl get a date column.

Use the DATEADD(<dates>,<number_of_intervals>,<interval>) to get the date column of same month last year, current year and future year.

CALCULATE( SUM('ACTUAL'[VALUES]) , DATEADD('DIM_DATE' ,-1, YEAR) ) // last year

CALCULATE( SUM('ACTUAL'[VALUES]) , DATEADD('DIM_DATE' ,0, YEAR) ) // current year

CALCULATE( SUM('ACTUAL'[VALUES]) , DATEADD('DIM_DATE' ,1, YEAR) ) // future year

Best Regards

Community Support Team _ chenwu zhu

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