Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
Hi All,
I am working with the fiscal calendar below:
January: 01/01/2018 - 01/26/2016
February: 01/27/2018 - 02/23/2018
I would like to create a new column in Power Query that identifies the fiscal month a customer wants his/her order. If Customer1 wants his product on January 5, 2018 and Customer2 wants her product on January 29, 2018 I would like the fiscal month to be returned in a new column. Fiscal Month.
So I would like the below to happen where columns "Customer" and "Requested Date" are given I want this query to return the column "Fiscal Date:"
Customer | Requested Date | Fiscal Date
Customer1 | 01/05/2018 | 01/01/2018
Customer2 | 01/29/2018 | 02/01/2018
I am aware that something similar to Excel and DAX's IF AND statement would need to be used, but I am not very familiar with PowerQuery.
Thanks in Advance.
Solved! Go to Solution.
Hi @Anonymous,
Please refer to the snapshot below and try the formula. Your sample is too short. So I just hardcode these rules.
if [Requested Date] >= #date(2018, 1, 1) and [Requested Date] <= #date(2018, 1, 26)
then #date(2018, 1, 1)
else if [Requested Date] >= #date(2018, 1, 27) and [Requested Date] <= #date(2018, 2, 23)
then #date(2018, 2, 1)
else #date(9999, 12, 31)
Best Regards,
Dale
Hi @Anonymous,
Please refer to the snapshot below and try the formula. Your sample is too short. So I just hardcode these rules.
if [Requested Date] >= #date(2018, 1, 1) and [Requested Date] <= #date(2018, 1, 26)
then #date(2018, 1, 1)
else if [Requested Date] >= #date(2018, 1, 27) and [Requested Date] <= #date(2018, 2, 23)
then #date(2018, 2, 1)
else #date(9999, 12, 31)
Best Regards,
Dale
IF Primary Discipline is blank Get value from Discipline & even if Discipline is blank Get value from Attribute 4 Column and Add new Column “WIR Discipline”
This works for what I need it for!
Turns out there is a difference between a date column and a dattime column so I had to adjust your solution slightly to reflect this. I can see how updating this once a new fiscal calendar comes out may be a pain though.
Do you have a calendar table in your model that represents your fiscal calendar? As long as that calendar table has a single record for each date, then you could simply merge the calendar table to your customer table and bring in either the fiscal month name (January, February...) or bring in the first date of that fiscal month (you would need that column to exist in your calendar table).
This sounds like a simple option. Do you have any links you can refer me to? I know I can easily create the table.
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.