Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
Vinay07
Helper II
Helper II

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

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.

Vinay07_0-1724434385738.png

 

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

Vinay07_1-1724434424160.png

 

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

6 REPLIES 6
Vinay07
Helper II
Helper II

Hi Dale,

 

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

 

OUTPUT: 

Vinay07_0-1724469948854.png

 

 

 

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
Frequent Visitor

Hi,

 

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

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
Frequent Visitor

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?

DaleT_0-1724812951366.png

 

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

 

Dale

DaleT
Frequent Visitor

Hi,

 

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

DaleT_0-1724441174348.png

 

Dale

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.