Forum Discussion

TrulsB's avatar
TrulsB
Frequent Visitor
6 years ago
Solved

Count rows on selected months

I have a  sharepoint list with a lot of projects. I do aloso have a date table with date, week, monty, year, quartal eg.  The columns in the sharepoint list is Projectname (text),  Start date (date)...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    6 years ago

    Hi,

    Since i have not understood the logic of the Yes/No, i have not been able to generate that column.  You may download my PBI file from here.

    Hope this helps.

  • dax's avatar
    dax
    6 years ago

    Hi TrulsB,

    You could refer to Ashish_Mathur 's file, if you want to get total in column and row, you could turn subtotal button like below

    If you want to get start date- stop date in last column, you could try to use below measure to replace measure in above file

    Measure =
IF (
    HASONEVALUE ( 'Attribute order'[Attribute] ),
    DISTINCTCOUNT ( Data[Projectname] ) + 0,
    CALCULATE (
        DISTINCTCOUNT ( Data[Projectname] ),
        FILTER ( 'Attribute order', 'Attribute order'[Attribute] = "Start date" )
    )
        - CALCULATE (
            DISTINCTCOUNT ( Data[Projectname] ),
            FILTER ( 'Attribute order', 'Attribute order'[Attribute] = "Stop date" )
        ) + 0
)

    Best Regards,
    Zoe Zhi

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

amitchandak's avatar
amitchandak
Super User
6 years ago

Try creating formula's like this

Open Month = CALCULATE(sum(Sales[Sales Amount]),filter(sales,Sales[Sales Date]<=MAXx('Date',ENDOFMONTH('Date'[Date Filer]))
&& Sales[Sales Date]>=Minx('Date',startOFMONTH('Date'[Date Filer]))))

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601