Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only
  • 1.  Count Number of Months from data sample set

    Posted Aug 04, 2022 11:51 AM

    Hello,

     

    I am trying to do a distinct count on number of months listed in a table based on Document Date.

    The following formulas return a value of 12 even though only 7 months are listed in the actual dataset (Jan-Jul)

     

    Distinctcount(Sales[DocumentDate].[Month])

    Countrows(Sales[DocumentDate].[Month])

     

    Any idea why these return 365 days or 12 months instead of 7? I confirmed that the start and end dates are January to July and that there are no other entries.

     

    Thanks



  • 2.  RE: Count Number of Months from data sample set

    Posted Aug 07, 2022 09:52 PM
    Tente

    Medida =
    COUNTROWS ( SUMMARIZE ( Sales, Sales[DocumentDate].[MonthNo] ) )​


    ------------------------------
    Vilmar Santos
    ------------------------------



  • 3.  RE: Count Number of Months from data sample set

    Posted Aug 09, 2022 05:26 PM
    This is what I tried but it returned value of 12 for the entire year.
    I did get it to work though by calculating the average for each month first. Here's what I used:

    YTD Monthly Avg =
    CALCULATE( [Monthly Avg],
    FILTER(ALL('Calendar'),
    'Calendar'[Month] <= MAX('Calendar'[Year]) &&
    'Calendar'[Year] = MAX('Calendar'[Year]) ) )


    ------------------------------
    Sana Ghansar
    Sabre Limited
    Cambridge ON
    519-585-7524
    ------------------------------



  • 4.  RE: Count Number of Months from data sample set

    Posted Aug 09, 2022 07:33 PM
    Que estranho não ter dado certo pra você. Mas que bom que conseguiu fazer de outra maneira.

    Este é o que eu fiz por aqui. Repare que só contou os sete meses.



    ------------------------------
    Vilmar Santos
    ------------------------------