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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors