Forum Discussion

jackypatel's avatar
jackypatel
Icon for Helper I rankHelper I
1 year ago

Need Last Month Data in My Card View

I have a calender that contain April is the starung Month & March is the Last Month of the year. For Example i have data like Month & Return as mention below   Month Return Apr-23 53690 ...
Need Help
PijushRoy's avatar
PijushRoy
Icon for Community Champion rankCommunity Champion
1 year ago

Hi jackypatel 

Please find the solution, attached PBIX

Please check the two calculated column in PBIX file

 



Measure = VAR _LatestMonth = IF(SELECTEDVALUE('Table'[Return])=0,MAX('Table'[Fiscal_Month_No])-1,MAX('Table'[Fiscal_Month_No]))
RETURN
CALCULATE(SUM('Table'[Return]),'Table'[Fiscal_Month_No]=_LatestMonth)

 

jackypatel's avatar
jackypatel
Icon for Helper I rankHelper I
1 year ago

Thanx for you support, But Its Not Working. If i added Mar-25 Data than it is also Showing Feb-25 Data.

 

Plz guide us further 

  • PijushRoy's avatar
    PijushRoy
    Icon for Community Champion rankCommunity Champion
    1 year ago

    Hi jackypatel 
    I have check with Feb 2025 data and without data, now working.
    Please use the measure in same PBIX file

    Latest Month Return = 
VAR SelectedYear = SELECTEDVALUE('Table'[Year]) 
VAR MaxMonthNo =
    CALCULATE(
        MAX('Table'[Fiscal_Month_No]), 
        FILTER('Table', 'Table'[Year] = SelectedYear)
    )
VAR LatestMonthReturn =
    CALCULATE(
        MAX('Table'[Return]),
        FILTER('Table', 'Table'[Fiscal_Month_No] = MaxMonthNo && 'Table'[Year] = SelectedYear)
    )
VAR PreviousMonthReturn =
    CALCULATE(
        MAX('Table'[Return]), 
        FILTER('Table', 'Table'[Fiscal_Month_No] = MaxMonthNo - 1 && 'Table'[Year] = SelectedYear)
    )
RETURN
    IF(
        ISBLANK(LatestMonthReturn), 
        PreviousMonthReturn, 
        LatestMonthReturn 
    )
    • jackypatel's avatar
      jackypatel
      Icon for Helper I rankHelper I
      1 year ago

      Thank you... Your Dax is working.

       

      But in my Dax is not Showing the value. Because Calender (Date) & Return Value is different  file. 

       

      So what shoud i do for this???

      Kindly Help

       

      • PijushRoy's avatar
        PijushRoy
        Icon for Community Champion rankCommunity Champion
        1 year ago

        Hi jackypatel 
        You can create a relationship between Table and Calendar with a Date column (if you have one) or else Fiscal Month no column (I can see the column available in your data).

        The use the DAX 

        Latest Month Return = 
VAR SelectedYear = SELECTEDVALUE('Calendar'[Year]) 
VAR MaxMonthNo =
    CALCULATE(
        MAX(' Calendar '[Fiscal_Month_No]), 
        FILTER(' Calendar', ' Calendar '[Year] = SelectedYear)
    )
VAR LatestMonthReturn =
    CALCULATE(
        MAX('Table'[Return]),
        FILTER(' Calendar ', ' Calendar '[Fiscal_Month_No] = MaxMonthNo && ' Calendar'[Year] = SelectedYear)
    )
VAR PreviousMonthReturn =
    CALCULATE(
        MAX('Table'[Return]), 
        FILTER(' Calendar', ' Calendar'[Fiscal_Month_No] = MaxMonthNo - 1 && ' Calendar'[Year] = SelectedYear)
    )
RETURN
    IF(
        ISBLANK(LatestMonthReturn), 
        PreviousMonthReturn, 
        LatestMonthReturn 
    )


        If not work, Please attached PBIX file link