Forum Discussion

vvv's avatar
vvv
New Member
3 years ago

PowerBI Table and Chart

Hi, Below is my sample data and result needed, i am trying in calculated column but its giving count for each month , but not like i am expecting the result set.

 

Please help me with the DAX or Calculated column

I have Data Like This   
IDStartDateEndDate 
15/1/20225/31/2022 
16/1/20226/30/2022 
110/1/202210/30/2022 
111/1/202211/30/2022 
111/1/202211/30/2022 
112/1/202212/31/2022 
112/1/202212/31/2022 
11/1/20231/31/2023 
    
    
Result neededMonth YearCount 
 OCT 202235/1/2022 to 10/30/2022
 NOV-202255/1/2022 to 11/30/2022
 DEC-202275/1/2022 to 12/31/2022
 JAN-202385/1/2022 to 1/31/2023

4 Replies

    • vvv's avatar
      vvv
      New Member
      3 years ago

      Hi,

      I did this but , i am getting same count count for all Months
      Oct 22 - 8

      Nov -22 8
      Dec  22 8

       

      Am i missing anything

  • JW_van_Holst's avatar
    JW_van_Holst
    Resolver IV
    3 years ago

    This are the two tables I use:

    and this is the DAX code

    _result = 
VAR EOM = MAX(MonthYear[End of Month])
VAR _filter = FILTER(Transactions, Transactions[EndDate] <= EOM)
RETURN
COUNTROWS(_filter)

    and this is the result:

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    Hi vvv 

    you have to have a Date table minimum with Date and Year Month columns. The you can use

    Count =
    SUMX (
    VALUES ( 'Date'[Year Month] ),
    CALCULATE (
    VAR MaxDate =
    MAX ( 'Date'[Date] )
    VAR MinDate =
    MIN ( 'Date'[Date] )
    RETURN
    COUNTROWS (
    FILTER ( 'Table', 'Table'[StartDate] <= MaxDate && 'Table'[EndDate] >= MinDate )
    )
    )
    )