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

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

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)?

Amardeep Bhingardeve
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 

 

Amardeep Bhingardeve
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

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

October Power BI Update Carousel

Power BI Monthly Update - October 2025

Check out the October 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

Top Solution Authors
Top Kudoed Authors