Urgent Help on DAX - Previous Year/Months data on dates

[Vinay07]

Hi All,

Please help on the DAX, below is the screenshots and sample data pasted in the content.

I have tried so many approaches, but unable to reach OUTPUT in Power BI. Please help.

INPUT: Here is the screenshot of data coming from database loading into the Power BI desktop.

OUTPUT: Here is the screenshot of OUTPUT layout to Visualize in "Table Visual".

CALCULATION: Here is the condition/logic need to work on DAX.

Example: If Application date is 2023-10

(01): Month of Extraction date - 12 month (2023/October - 12 months ⇒ 2022/October)

(01)Data of 2022-10(12month ago of Extraction date)
(02)Data of 2022-11(11month ago of Extraction date)
(03)Data of 2022-12(10month ago of Extraction date)
(04)Data of 2023-01(9month ago of Extraction date)
(05)Data of 2023-02(8month ago of Extraction date)
(06)Data of 2023-03(7month ago of Extraction date)
(07)Data of 2023-04(6month ago of Extraction date)
(08)Data of 2023-05(5month ago of Extraction date)
(09)Data of 2023-06(4month ago of Extraction date)
(10)Data of 2023-07(3month ago of Extraction date)
(11)Data of 2023-08(2month ago of Extraction date)
(12)Data of 2023-09(1month ago of Extraction date)
(13)Data of 2023-10(0month ago of Extraction date)

SAMPLE DATA: Here is the sample data to work and load the data in Power BI.

Direct copy the data and paste it in Excel

Earliest APPL_CTL_PRCES_DT POLICY_7DIGIT BUYING_YEARS_MONTHS INCREASED_LIVING_BENEFIT CUMULATIVE_INCREASED_SURVIVAL_BENEFITS
2023-10-12 8514723 2022-10-01 4 98030
2023-10-12 8514723 2022-11-01 4 105034
2023-10-12 8514723 2022-12-01 4 112039
2023-10-12 8514723 2023-01-01 4 119044
2023-10-12 8514723 2023-02-01 5 126049
2023-10-12 8514723 2023-03-01 5 133055
2023-10-12 8514723 2023-04-01 5 140060
2023-10-12 8514723 2023-05-01 6 147066
2023-10-12 8514723 2023-06-01 6 154073
2023-10-12 8514723 2023-07-01 6 161080
2023-10-12 8514723 2023-08-01 6 168087
2023-10-12 8514723 2023-09-01 7 175094
2023-10-12 8514723 2023-10-01 7 182101
2017-06-01 6951290 2016-06-01 0 0
2017-06-01 6951290 2016-07-01 0 0
2017-06-01 6951290 2016-08-01 0 0
2017-06-01 6951290 2016-09-01 0 1
2017-06-01 6951290 2016-10-01 0 1
2017-06-01 6951290 2016-11-01 0 1
2017-06-01 6951290 2016-12-01 0 2
2017-06-01 6951290 2017-01-01 0 2
2017-06-01 6951290 2017-02-01 0 2
2017-06-01 6951290 2017-03-01 0 2
2017-06-01 6951290 2017-04-01 0 3
2017-06-01 6951290 2017-05-01 0 3
2017-06-01 6951290 2017-06-01 0 3
2020-06-12 6951290
2020-06-12 6951290 2019-07-01 0 3
2020-06-12 6951290 2019-08-01 0 3
2020-06-12 6951290 2019-09-01 0 3
2020-06-12 6951290 2019-10-01 0 3
2020-06-12 6951290 2019-11-01 0 3
2020-06-12 6951290 2019-12-01 0 3
2020-06-12 6951290 2020-01-01 0 3
2020-06-12 6951290 2020-02-01 0 3
2020-06-12 6951290 2020-03-01 0 3
2020-06-12 6951290 2020-04-01 0 3

[Vinay07]

Hi Dale,

Yes, I have tried with DATEDIFF function. What is next step/process to get OUTPUT in Power BI ?

OUTPUT: 

[v-tianyich-msft]

Hi @Vinay07 ,

This looks more like matrix visual, try to aggregate your years, put them into matrix column, and the calculated aggregated values into Values.

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

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

[DaleT]

Hi,

There are so many column in the output. Which one is related to DATEDIFF? What is the logic to calculate it?

[Vinay07]

Hi Dale,

The logic behind the calculation is,

Whenever "POLICY_7DIGIT" generating newly "APPL_CTL_PRCES_DT" created backend. From the "APPL_CTL_PRCES_DT" user wants to check previous year data in table visual for all "13 BUYING_YEARS_MONTHS" in the below calculation with respective "INCREASED_LIVING_BENEFIT", "CUMULATIVE_INCREASED_SURVIVAL_BENEFITS".

However, from the database side for each "POLICY_7DIGIT" number previous year data is alredy converted in "13 BUYING_YEARS_MONTHS" with respective in "APPL_CTL_PRCES_DT". 

Here the OUTPUT need to visualise in Table visual format with sample data given in above content. Please help

[DaleT]

Hi,

According to the data and your description, it seems the result is already processed by the database. You just need to show it up. Please refer to the image below and the pbix file. Is this what you need?

PBIX file link: https://drive.google.com/file/d/1o3rn6jkY2O1E4Ze7Pp7teC2Y9w-PB52Y/view?usp=sharing 

Dale

[DaleT]

Hi,

Try the DATEDIFF funtion like in the image below. Make sure the data type is DATE.

Dale