Days in Quarter (custom colomn)

[Anonymous]

Hello,

I want to extract the days in a quarter from 2 dates.

Is this possible? .

Example:

Start date 1-1-2019 

End date 31-12-2019

days Quarter 1, days quarter 2, days quarter3, days quarter 4

Or 

Start date 1-3-2019 

End date 31-7-2019

days Quarter 1, days quarter 2, days quarter3, days quarter 4

[v-diye-msft · Accepted solution]

Hi @Anonymous 

I created  sample table as below:

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,

Dina Ye

[Anonymous]

@v-diye-msft Wow no wonder i could no do it. its huge!

I am going to proces this in Power BI. it can take a will but i will respond.

Thank you in adance

[Anonymous]

Wonderfull! it works. i just hav eto make some addjustmenst in cause there are null values.

the cell retruns an error.