cancel
Showing results for
Did you mean:
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
Community Support

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

Then create 2 parameters as Start and End:

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:

Finally , invoke custome function in fact table:

The results shown as below:

Best regards,

Lucien

Post Prodigy

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

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

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 !!