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