Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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.

 

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

"Calculated Column"
"DAX Commands and Tips"
'Need help' 'DAX'
DAX help needed

6 Replies

  • DaleT's avatar
    DaleT
    Icon for Resolver II rankResolver II
    2 years ago

    Hi,

     

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

     

    Dale

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Dale,

     

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

     

    OUTPUT: 

     

     

     

    • DaleT's avatar
      DaleT
      Icon for Resolver II rankResolver II
      2 years ago

      Hi,

       

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        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

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi Anonymous ,

       

      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.