Forum Discussion

Larry130's avatar
Larry130
Frequent Visitor
9 years ago
Solved

Current month vs Previous month

Hi! Could you guys give some help? I'm struggling here. I need to do a calculation between the current x last month, but I'll not use a date dimension to show each month in a table/graph. Because ...
Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    Hi Larry130,

     

    If I understand you correctly, the formula below should work in your scenario. :smileyhappy:

    PrevMonth =
VAR currentMonth =
    MONTH ( TODAY () )
VAR currentYear =
    YEAR ( TODAY () )
RETURN
    IF (
        currentMonth = 1,
        CALCULATE (
            [ID Alert],
            FILTER (
                ALL ( 'Calendar'[Date] ),
                YEAR ( 'Calendar'[Date] )
                    = currentYear - 1
                    && MONTH ( 'Calendar'[Date] ) = 12
            )
        ),
        CALCULATE (
            [ID Alert],
            FILTER (
                ALL ( 'Calendar'[Date] ),
                YEAR ( 'Calendar'[Date] ) = currentYear
                    && MONTH ( 'Calendar'[Date] )
                        = currentMonth - 1
            )
        )
    )

     

    Regards

v-ljerr-msft's avatar
v-ljerr-msft
Microsoft Employee
9 years ago

Hi Larry130,

 

If I understand you correctly, the formula below should work in your scenario. :smileyhappy:

PrevMonth =
VAR currentMonth =
    MONTH ( TODAY () )
VAR currentYear =
    YEAR ( TODAY () )
RETURN
    IF (
        currentMonth = 1,
        CALCULATE (
            [ID Alert],
            FILTER (
                ALL ( 'Calendar'[Date] ),
                YEAR ( 'Calendar'[Date] )
                    = currentYear - 1
                    && MONTH ( 'Calendar'[Date] ) = 12
            )
        ),
        CALCULATE (
            [ID Alert],
            FILTER (
                ALL ( 'Calendar'[Date] ),
                YEAR ( 'Calendar'[Date] ) = currentYear
                    && MONTH ( 'Calendar'[Date] )
                        = currentMonth - 1
            )
        )
    )

 

Regards

mnarmeen's avatar
mnarmeen
Advocate I
9 years ago

Dear v-ljerr-msft ,

 

I have a similar problem but the following code shows blank. My scenario is to calculate max difference between two time duration (hours) which is already a calculated column (DiffDeliveryTime). Slight difference is that I don't want to use calendar table to filter out my values instead I want to use my own table in which dates are stored. The dates are repeating as many deliveries are scheduled on the same day as well.

 

PrevMonth =
VAR currentMonth =
    MONTH ( invoice[DeliveryDate] )
VAR currentYear =
    YEAR ( invoice[DeliveryDate] )
RETURN
    IF (
        currentMonth = 1,
        CALCULATE (
            MAX(DiffDeliveryTime),
            FILTER (
                ALL ( invoice[DeliveryDate]),
                YEAR ( invoice[DeliveryDate] )
                    = currentYear - 1
                    && MONTH ( invoice[DeliveryDate] ) = 12
            )
        ),
        CALCULATE (
            MAX(DiffDeliveryTime),
            FILTER (
                ALL ( invoice[DeliveryDate] ),
                YEAR ( invoice[DeliveryDate] ) = currentYear
                    && MONTH ( invoice[DeliveryDate] )
                        = currentMonth - 1
            )
        )
    )