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

[Amardeep100115]

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

[v-luwang-msft]

Hi @Amardeep100115 ，

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

[Amardeep100115]

@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 

 

[amitchandak]

@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