Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
fguijarrob
Frequent Visitor

Current Month vs Previous Month and values when changing the year

Hello,

I am trying to compare 2 columns: 

  - Current month values

vs.

  - Previous Month Values

 

To do that, I´ve developed 2 Measures:

- Total of Current Flight Hours

      

 

Total Horas Vuelo Mes Actual = 
CALCULATE(
    (SUM('PROGRAMACIÓN REAL ENERO 2022'[Duracion del vuelo (sg)])/3600))

 

and 

- Total of Previous Month Flight Hours

 

Total Horas Vuelo Mes Anterior = 
CALCULATE(Medidas[Total Horas Vuelo Mes Actual],
FILTER(ALL(dDate), 
                     dDate[Año]=SELECTEDVALUE(dDate[Año]) &&
                     dDate[MesNro]=SELECTEDVALUE(dDate[MesNro])-1)               
            )

 

 

I also have the porcentage (%) between Flight hours of current month and previous month:

 

%Dif. Horas de Vuelo Mes Actual = (([Total Horas Vuelo Mes Actual]-[Total Horas Vuelo Mes Anterior])/([Total Horas Vuelo Mes Anterior]))

 

However when I´m visualizing those three columns, when changing the year (for example, between Dec 2021 and Jan 2022), previous month is BLANK (see the picture attached)

fguijarrob_0-1660996234030.png

 

How can I manage it to get the value from December??? 

Thank you for your help!!

1 ACCEPTED SOLUTION

@fguijarrob 

You need to add another column [Year Month Sequential Number] 

=
RANKX ( TableName, [Year_Month_ Value],, ASC, Dense )

 

View solution in original post

5 REPLIES 5
tamerj1
Super User
Super User

Hi @fguijarrob 

when you have the month January, the month number is 1 therefore, 1 - 1 is 0 which is a month that do not exist. Therefore, the result is blank. You need to use a Year Month sequential number column in your date table which can simply be a dense rank (ascending) of the year month number. And the year month number can be just YEAR ([Date] ) *100 + MONTH ( [Date] ). Please let me know if you need any help on this. 

Thank you Tamerj1!

I´ve created a new column (Year_Month_Value) in the calendar table (dDate)

Year_Month_Value = dDate[Year]*100+dDate[MonthNumber]

fguijarrob_1-1661028320156.png

 

The current Flight Hours is:

Total Horas Vuelo Mes Actual = 
CALCULATE(
    (SUM('PROGRAMACIÓN REAL ENERO 2022'[Duracion del vuelo (sg)])/3600))

 

So for column "Previous Flight Hours" I´ve also wrote:

 

Total Horas Vuelo Mes Anterior = 
CALCULATE(Medidas[Total Horas Vuelo Mes Actual],
FILTER(ALL(dDate), 
                    dDate[Year_Month_Value]=SELECTEDVALUE(dDate[Year_Month_Value])-1) 
)

 

However, on January is not appearing the previous month as the picture shows:

 

fguijarrob_0-1661027905813.png

 

Can you please help me? 

Thank you for your time!

@fguijarrob 

You need to add another column [Year Month Sequential Number] 

=
RANKX ( TableName, [Year_Month_ Value],, ASC, Dense )

 

Great! That works!

I´ll start to use RANKX!

fguijarrob_0-1661033523164.png

 

daXtreme
Solution Sage
Solution Sage

Hi @fguijarrob 

 

You should use the time-intel functions to do such calculations. Doing them by hand (like you do) will invariably lead to problems (and they just have). Please start here: Time Intelligence in Power BI Desktop - SQLBI

 

Your issue stems from the fact that you're trying to subtract 1 from 1 (the number for January), which is 0, but it should be 12 (to go to December last year). But it's messy and error-prone to do such calculations by hand, as I said. Please use time-intel. That's what it's for.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors