# Calendar agregation

• ### Question

• Hello,

Il would like to manipulate some calendar agregation in my calculation such as "Number of days per month" and "Number of days per year".

If It is easy to make some sub total of the Fact table, it is more complicated with dimensions.

My data sampes are :

 Pdt KPI Année Mois QTY prix Calculate Date PDT1 Value1 2017 01 10 50 01/01/2017 PDT2 Value2 2017 02 20 51 01/02/2017 PDT3 Value3 2017 03 30 53 01/03/2017 PDT4 Value1 2018 01 20 54 01/01/2018 PDT5 Value2 2018 02 30 100 01/02/2018 PDT6 Value3 2018 03 40 80 01/03/2018 PDT4 Value1 2019 01 30 70 01/01/2019 PDT5 Value2 2019 02 40 60 01/02/2019 PDT6 Value3 2019 03 50 90 01/03/2019 PDT1 Value1 2017 04 10 50 01/04/2017 PDT2 Value2 2017 05 20 51 01/05/2017 PDT3 Value3 2017 06 30 53 01/06/2017

This table is linlked to a classic calendar table.

and a corresponding presentation without nulber of days

 Année KPI Mois Somme de QTY Montant Qty Année 2017 Value1 01 10 500 120 2017 Value1 04 10 500 120 2017 Value2 02 20 1020 120 2017 Value2 05 20 1020 120 2017 Value3 03 30 1590 120 2017 Value3 06 30 1590 120 Total 2017 120 6220 120 2018 Value1 01 20 1080 90 2018 Value2 02 30 3000 90 2018 Value3 03 40 3200 90 Total 2018 90 7280 90 2019 Value1 01 30 2100 120 2019 Value2 02 40 2400 120 2019 Value3 03 50 4500 120 Total 2019 120 9000 120 Total général 330 22500 330

Qty Année is the sum of Quantity by year.

I need the number of days by year and month.

Thursday, February 15, 2018 10:30 AM

### All replies

• Hi Jerome,

>>>I need the number of days by year and month.
In this scenario, please try below DAX formula:
```SelectedDaysCount :=
CALCULATE ( COUNTROWS ( 'Calendar' ), ALLSELECTED ( 'Calendar' ) )```

Best Regards
Willson Yuan
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

Friday, February 16, 2018 3:16 AM
• Hello Willson,

Here is a presentation of this calculation

 Année KPI Mois AnnéeMois Somme de QTY Montant SelectedDaysCount 2017 Value1 01 201701 10 500 730 2017 Value1 01 201702 730 2017 Value1 01 201703 730 2017 Value1 01 201704 730 2017 Value1 01 201705 730 2017 Value1 01 201706 730 2017 Value1 01 201707 730 2017 Value1 01 201708 730 2017 Value1 01 201709 730

You can see that the result is the total of calendar ligne.

It added somme blank line where there is no data.

I would like to have the total of days for each month not for the all calendar.

Thnaks

Friday, February 16, 2018 2:17 PM
• Excel 2016 Pro Plus with PowerPivot and Power Query (aka Get & Transform)
Calculate working days per month/year.
Calendar Table generated with PQ.
Extra: Average value per working day each month.
http://www.mediafire.com/file/3jmxjnxulw8auai/02_17_18.xlsx
http://www.mediafire.com/file/83hvyeu4pl8h866/02_17_18.pdf

Saturday, February 17, 2018 5:34 PM
• Hello,

the forula does the job

Days:=if([Cost_]=BLANK();BLANK();
CALCULATE(COUNTROWS('Calendar')))

It Calculate the number of days of the agregegate level.

If you don't test the blank value you have the result I had.

What If, if I want to have the total by year for each Ligne ?

Jerome

Tuesday, February 20, 2018 2:23 PM
• Excel 2016 Pro Plus with PowerPivot and Power Query (aka Get & Transform)
To include portion of another year, it was necessary to expand the 'Calendar' query.
This also allowed the previous 'Days' measure to be simplified.
http://www.mediafire.com/file/x2m0c0pnnh74sp3/02_17_18b.xlsx
http://www.mediafire.com/file/gm642j3g2zm9ksx/02_17_18b.pdf
Tuesday, February 20, 2018 7:25 PM
• OK

Thanks Herbert

Wednesday, February 21, 2018 9:03 AM