cancel
Showing results for 
Search instead for 
Did you mean: 
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
Super User
Super User

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

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
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors