cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Amardeep100115
Post Prodigy
Post Prodigy

How we can have days in qtr in power query ( M-Code)?

How we can have days in qtr in power query ( M-Code)?

AB
3 REPLIES 3
v-luwang-msft
Community Support
Community Support

Hi @Amardeep100115 ,

Pls refer the below sample:(the first rows is year start_date and end_date)

vluwangmsft_0-1669343831511.png

 

Then create 2 parameters as Start and End:

vluwangmsft_1-1669343831389.png

 

Add new blank query with below M code to count the days in each quarter :

Days Qtr 1:
let
    Source = List.Dates( Start, Number.From( End - Start) +1, #duration(1,0,0,0)),
    Custom1 = List.Select(Source, each Date.QuarterOfYear(_)=1),
    #"Calculated Count" = List.NonNullCount(Custom1)
in
#"Calculated Count"


Days Qtr 2:
let
    Source = List.Dates( Start, Number.From( End - Start) +1, #duration(1,0,0,0)),
    Custom1 = List.Select(Source, each Date.QuarterOfYear(_)=2),
    #"Calculated Count" = List.NonNullCount(Custom1)
in
#"Calculated Count"


Days Qtr 3:
let
    Source = List.Dates( Start, Number.From( End - Start) +1, #duration(1,0,0,0)),
    Custom1 = List.Select(Source, each Date.QuarterOfYear(_)=3),
    #"Calculated Count" = List.NonNullCount(Custom1)
in
#"Calculated Count"


Days Qtr 4:
let
    Source = List.Dates( Start, Number.From( End - Start) +1, #duration(1,0,0,0)),
    Custom1 = List.Select(Source, each Date.QuarterOfYear(_)=4),
    #"Calculated Count" = List.NonNullCount(Custom1)
in
#"Calculated Count"

Right click each query to create the function:

vluwangmsft_2-1669343831701.png

 

vluwangmsft_3-1669343831690.png

 

Finally , invoke custome function in fact table:

vluwangmsft_4-1669343832043.png

 

vluwangmsft_5-1669343831546.png

 

The results shown as below:

vluwangmsft_8-1669344028640.png

 

 

Best regards,

Lucien

@v-luwang-msft  & @amitchandak 

Please find the below calendar m-code that i am working on, Please help me with adding a column for days in Qtr and number. i mean  when qtr get start then days rows should have 1-90/91 days valuesand whenever new qtr get start again values will start from 1-90/91.  also if you could able to add week num (1- 13/14) in qtr please 

Calendar m-code

 

Thanks 

 

AB
amitchandak
Super User
Super User

@Amardeep100115 ,

If you have the start and end date of qtr, you can have

 

example for today's date

 

Duration.Days(Date.EndOfQuarter(DateTime.LocalNow())  - Date.StartOfQuarter(DateTime.LocalNow()) )

 

add +1 if needed



Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Learn Power BI P&L Statement || Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s ||
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!!! How to get Quick Help !!
Resources | Master Power BI !! Expertise Power BI !! Power BI For Tableau User !!

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.