Max month for max year

[Anonymous]

Hi everyone, 

I'm trying to calculate an inventory turnover with Power BI.

I want to divide those two columns : Volume that we have in stocks / volume that we sold (for that, it is ok, my measure is a simple divide)

And then I want to divide it by the last month of the last year 

I've tried this, but it doesn't work :

Max month = CALCULATE(

MAX('Company$Commision Entries'[Mois]),

FILTER('Company$Commision Entries',MAX('Company$Commision Entries'[Année nbre]))

)

It always says as a response "12", I have indeed 12 months for 2023 but I have only 5 for 2024, as it is related to may.

My months and years are in numbers. 

Does someone have an idea please ? 

Thanks 🙂

[mh2587]

InventoryTurnoverLastMonthLastYear = //Try this might help you
VAR LastYear = CALCULATE(
    MAX('Company$Commision Entries'[Année nbre]),
    ALL('Company$Commision Entries')
)
VAR MaxMonthLastYear = CALCULATE(
    MAX('Company$Commision Entries'[Mois]),
    FILTER(
        'Company$Commision Entries',
        'Company$Commision Entries'[Année nbre] = LastYear
    )
)
RETURN
DIVIDE(
    CALCULATE(
        SUM('Company$Commision Entries'[VolumeInStock]),
        FILTER(
            'Company$Commision Entries',
            'Company$Commision Entries'[Année nbre] = LastYear &&
            'Company$Commision Entries'[Mois] = MaxMonthLastYear
        )
    ),
    CALCULATE(
        SUM('Company$Commision Entries'[VolumeSold]),
        FILTER(
            'Company$Commision Entries',
            'Company$Commision Entries'[Année nbre] = LastYear &&
            'Company$Commision Entries'[Mois] = MaxMonthLastYear
        )
    ),
    0
)

[ManuelBolz · Accepted solution]

Hello @Anonymous 

If my post helped you, please give me a 👍kudos and mark this post with Accept as Solution.

If I understood you correctly, this should be your solution.

VAR LastYear = MAX('Company$Commision Entries'[Année nbre])
VAR MaxMonth = 
    CALCULATE(
        MAX('Company$Commision Entries'[Mois]),
        FILTER(
            'Company$Commision Entries',
            'Company$Commision Entries'[Année nbre] = LastYear
        )
    )
RETURN
    DIVIDE(
        [Volume in Stock], 
        [Volume Sold] * MaxMonth
    )

Best regards from Germany
Manuel Bolz

🟦Follow me on LinkedIn
🟨How to Get Your Question Answered Quickly
🟩Fabric Community Conference
🟪My Solutions on Github

[Anonymous]

Thank you for your help !! it works 😄