locked
Calendar agregation RRS feed

  • 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.

    Thanks in advance

    Thursday, February 15, 2018 10:30 AM

Answers

All replies

  • Hi Jerome,

    Thanks for your question. 

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