Previous 6 Months Per Month

[Anonymous]

I have a table of bugs that has two main fields: environment and created date. I need to get the total number of bugs by environment for the past 6 months for each month. I tried many solutions I found online but nothing seemed to work. 

I currently have multiple measures that calculate the number of bugs by environment per month, this is what those look like:

Production Bugs = COUNTROWS(FILTER(Bugs, Bugs[env] = "Production" && Bugs[createdDate] = Bugs[createdDate]))+0

I have a feeling I am making this much more complicated than it needs to be, but can't seem to get it working. I am relatively new to Power BI and DAX, although I do know the basics.

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[v-shex-msft · Accepted solution]

Hi @Anonymous,

You can try to use the following measure formula if it meets your requirement:

Production Bugs =
VAR currDate =
    MAX ( Bugs[createdDate] )
RETURN
    COUNTROWS (
        FILTER (
            ALLSELECTED ( Bugs ),
            Bugs[env] = "Production"
                && Bugs[createdDate]
                    >= DATE ( YEAR ( currDate ), MONTH ( currDate ) - 6, DAY ( currDate ) )
                && Bugs[createdDate] <= currDate
        )
    ) + 0

Regards,

Xiaoxin Sheng

[amitchandak]

@Anonymous , you can use relative date slicer or this approach

https://www.youtube.com/watch?v=duMSovyosXE

relative date slicer

https://docs.microsoft.com/en-us/power-bi/visuals/desktop-slicer-filter-date-range

[Anonymous]

I need the actual data as I need to use it for other calculations. So it would need to be a measure I think.