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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
ItoDiaz
Helper I
Helper I

Select previous month data

Dear All, 

 

In this code, I need to select data of my previous month, it must take into account the case when my previous month is december. 

 

I have tried using a selection of data of my Previous and my current year :

#"Selectionannées" = Table.SelectRows(#"Colonne conditionnelle ajoutée", each ([ANNEE] = Currentyear or [ANNEE] = Previousyear)),

 

It "works" if my previous month is december, but for the reste of the months it generates conflicts and duplications when I apply my step :

 

#"AjoutCleTmp" = Table.AddColumn(#"MoisPrecedent","CleTmp", each Number.ToText(Currentmonth) & "-" & Number.ToText(Currentyear) & "-" & [Attribute]),

As I have data of my previous month for both previous and current year!! 

 

I have tried to select my previous month using this code : 

 

//#"MoisPrecedent" = Table.SelectRows(#"Colonne conditionnelle ajoutée", each if
//Currentmonth=1
//then
//Table.SelectRows(#"Colonne conditionnelle ajoutée", each ([MOIS] = Previousmonth and [ANNEE] = Previousyear))
//else
//Table.SelectRows(#"Colonne conditionnelle ajoutée", each ([MOIS] = Previousmonth))),

 

But query M send the message saying it's not possible to convert a table type in fonction type... and vice versa. 

 

How could I select only previous month data? I'm not a query M expert, I would appreciate  step by step explanations 

 

Thanks for your help! 

 

 

 

let
Source = FL_ET3_KPI,

TodaysDate = Date.From(DateTimeZone.FixedUtcNow()),
Currentmonth=Date.Month(TodaysDate),
Previousmonth=Date.Month(Date.AddMonths(TodaysDate,-1)),
Currentyear=Date.Year(TodaysDate),
Previousyear=Date.Year(Date.AddYears(TodaysDate,-1)),

#"Autres colonnes supprimées" = Table.SelectColumns(FL_ET3_KPI,{"CHRONO", "SITE", "LIGNE", "DATE", "PRODUIT", "VALIDITE_BOBINE", "MOIS", "SEMAINE", "CONFORMITE BOBINE_PMPP", "CONFORMITE BOBINE_PCP", "CONFORMITE PCP_MOYENNE", "CONFORMITE PP_MOYENNE", "Concentration DG_capteur", "Temperature DG", "Debit Circulation Brosses", "Debit Rampes Brosses", "Conc DKP_Capteur", "Temp DKP", "Deconcentr DKP", "Conc Fer DKP", "Debit Rincage 1 DKP_cascade", "Debit Rincage 2 DKP", "Pression Gr 1", "Stanneux Etamage AN1", "Acidite Etamage AN1", "Debit Circulation Electrolyte", "Volumes Etamage", "Acidite Rincage 1 Etamage", "Stanneux Rincage 1 Etamage", "Acidite Rincage 2 _ AN1", "Stanneux Rincage 2 _ AN1", "Pression Groupe 3", "Pression Groupe 4", "Debit Air Chaud Secheur 1", "Temperature Air Chaud Secheur 1", "Debit Rampe SUP _ BdT", "Debit Rampe INF _ BdT", "Temperature BdT", "Concentration Bichromate TC_Capteur", "pH _ TC_Capteur", "Temperature TC", "Debit Rincage 2 TC", "Chromates Rincages 2 TC", "Pression Groupe 5", "Debit Laveur Vapeur", "Pression Groupe 6", "Pression Groupe 7", "Debit Air Chaud Secheur 3", "Temperature Air Chaud Secheur 3", "Tension Pre_ioniseur SUP", "Intensite Pre_Ioniseur SUP", "Tension Pre_ioniseur INF", "Intensite Pre_ioniseur INF", "Tension Planques Repulsives", "Intensite Plaques Repulsives"}),
PreviousStep = Table.TransformColumnTypes(#"Autres colonnes supprimées",{{"CHRONO", Int64.Type}, {"SITE", type text}, {"LIGNE", type text}, {"DATE", type date}, {"PRODUIT", type text}, {"VALIDITE_BOBINE", type any}, {"MOIS", Int64.Type}, {"SEMAINE", Int64.Type}, {"CONFORMITE BOBINE_PMPP", Int64.Type}, {"CONFORMITE BOBINE_PCP", Int64.Type}, {"CONFORMITE PCP_MOYENNE", type number}, {"CONFORMITE PP_MOYENNE", type number}, {"Concentration DG_capteur", Int64.Type}, {"Temperature DG", Int64.Type}, {"Debit Circulation Brosses", Int64.Type}, {"Debit Rampes Brosses", Int64.Type}, {"Conc DKP_Capteur", Int64.Type}, {"Temp DKP", Int64.Type}, {"Deconcentr DKP", Int64.Type}, {"Conc Fer DKP", Int64.Type}, {"Debit Rincage 1 DKP_cascade", Int64.Type}, {"Debit Rincage 2 DKP", Int64.Type}, {"Pression Gr 1", Int64.Type}, {"Stanneux Etamage AN1", Int64.Type}, {"Acidite Etamage AN1", Int64.Type}, {"Debit Circulation Electrolyte", Int64.Type}, {"Volumes Etamage", Int64.Type}, {"Acidite Rincage 1 Etamage", Int64.Type}, {"Stanneux Rincage 1 Etamage", Int64.Type}, {"Acidite Rincage 2 _ AN1", Int64.Type}, {"Stanneux Rincage 2 _ AN1", Int64.Type}, {"Pression Groupe 3", Int64.Type}, {"Pression Groupe 4", Int64.Type}, {"Debit Air Chaud Secheur 1", Int64.Type}, {"Temperature Air Chaud Secheur 1", Int64.Type}, {"Debit Rampe SUP _ BdT", Int64.Type}, {"Debit Rampe INF _ BdT", Int64.Type}, {"Temperature BdT", Int64.Type}, {"Concentration Bichromate TC_Capteur", Int64.Type}, {"pH _ TC_Capteur", Int64.Type}, {"Temperature TC", Int64.Type}, {"Debit Rincage 2 TC", Int64.Type}, {"Chromates Rincages 2 TC", Int64.Type}, {"Pression Groupe 5", Int64.Type}, {"Debit Laveur Vapeur", Int64.Type}, {"Pression Groupe 6", Int64.Type}, {"Pression Groupe 7", Int64.Type}, {"Debit Air Chaud Secheur 3", Int64.Type}, {"Temperature Air Chaud Secheur 3", Int64.Type}, {"Tension Pre_ioniseur SUP", Int64.Type}, {"Intensite Pre_Ioniseur SUP", Int64.Type}, {"Tension Pre_ioniseur INF", Int64.Type}, {"Intensite Pre_ioniseur INF", Int64.Type}, {"Tension Planques Repulsives", Int64.Type}, {"Intensite Plaques Repulsives", Int64.Type}}),
#"Personnalisée ajoutée" = Table.AddColumn(PreviousStep, "ANNEE", each Date.Year([DATE])),
#"Lignes filtrées" = Table.SelectRows(#"Personnalisée ajoutée", each ([MOIS] <> null)),
DateList = List.Transform(List.Distinct(#"Lignes filtrées"[MOIS]), each {Text.From(_), Percentage.Type}),
ListLight = Table.RemoveColumns(#"Lignes filtrées",{"SITE", "LIGNE", "DATE", "PRODUIT", "VALIDITE_BOBINE", "SEMAINE", "CONFORMITE BOBINE_PMPP", "CONFORMITE BOBINE_PCP", "CONFORMITE PCP_MOYENNE", "CONFORMITE PP_MOYENNE"}),
Columns = List.Select(Table.ColumnNames(ListLight), each _ <> "MOIS" and _ <> "CHRONO" and _ <> "ANNEE"),
GroupList = List.Transform(Columns, each {_, Expression.Evaluate( "each (List.Sum(["& _ & "]) / Table.RowCount(_))", [List.Sum = List.Sum, Table.RowCount = Table.RowCount]), Number.Type}),
#"Grouped Rows" = Table.Group(#"Lignes filtrées", {"MOIS" , "ANNEE"}, GroupList),

#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Grouped Rows", {"MOIS" , "ANNEE"}, "Attribute", "TauxMois"),
#"Personnalisée ajoutée4" = Table.AddColumn(#"Unpivoted Other Columns", "Cle", each Number.ToText([MOIS]) & "-" & Number.ToText([ANNEE]) & "-" & [Attribute]),

#"Add Key1" = Table.ReplaceKeys(#"Personnalisée ajoutée4",{[Columns={"MOIS"},Primary = false]}),
#"Add Key2" = Table.AddKey(#"Add Key1", {"Cle"}, true),
#"Colonne conditionnelle ajoutée" = Table.AddColumn(#"Add Key2", "Alarme", each if [TauxMois] <= 0.8 then true else false),

#"Selectionannées" = Table.SelectRows(#"Colonne conditionnelle ajoutée", each ([ANNEE] = Currentyear or [ANNEE] = Previousyear)),
#"MoisCourant" = Table.SelectRows(#"Selectionannées", each ([MOIS] = Currentmonth)),
//#"MoisPrecedent" = Table.SelectRows(#"Colonne conditionnelle ajoutée", each if
//Currentmonth=1 
//then 
//Table.SelectRows(#"Colonne conditionnelle ajoutée", each ([MOIS] = Previousmonth and [ANNEE] = Previousyear))
//else 
//Table.SelectRows(#"Colonne conditionnelle ajoutée", each ([MOIS] = Previousmonth))),

#"MoisPrecedent" = Table.SelectRows(#"Selectionannées", each ([MOIS] = Previousmonth)),
#"AjoutCleTmp" = Table.AddColumn(#"MoisPrecedent","CleTmp", each Number.ToText(Currentmonth) & "-" & Number.ToText(Currentyear) & "-" & [Attribute]),

#"MoisCourantUpdate" = Table.NestedJoin(#"MoisCourant", {"Cle"}, #"AjoutCleTmp", {"CleTmp"}, "MoisN-1", JoinKind.LeftOuter),
#"MoisN-1 développé" = Table.ExpandTableColumn(MoisCourantUpdate, "MoisN-1", {"Alarme"}, {"MoisN-1.Alarme"}),
#"Colonne conditionnelle ajoutée1" = Table.AddColumn(#"MoisN-1 développé", "AlarmeRedPCP", each if [Alarme] = true then true else if [#"MoisN-1.Alarme"] = true then true else false),
#"Lignes filtrées1" = Table.SelectRows(#"Colonne conditionnelle ajoutée1", each ([MOIS] = Currentmonth and [ANNEE] = Currentyear)),
#"Doublons supprimés" = Table.Distinct(#"Lignes filtrées1", {"Cle"})
in
#"Doublons supprimés"

 

 

 

1 ACCEPTED SOLUTION
PC2790
Community Champion
Community Champion

Hello,

 

If you have the date column with you, you can simple filter out the previous month's data as below:

PC2790_0-1615459871365.png

The corresponding M query code:

= Table.SelectRows(#"Previous Step", each Date.IsInPreviousMonth([Date]))

View solution in original post

1 REPLY 1
PC2790
Community Champion
Community Champion

Hello,

 

If you have the date column with you, you can simple filter out the previous month's data as below:

PC2790_0-1615459871365.png

The corresponding M query code:

= Table.SelectRows(#"Previous Step", each Date.IsInPreviousMonth([Date]))

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors
Top Kudoed Authors