Count Documents Issued within Certain Month

[nr83]

Hello,

I am hoping someone can help. I am very new to Power BI and fumbling my way through how it works with the help of this community and You Tube videos. If replying, please do so in an "idiots guide" way as I am not clued up on a lot of the functionality and terminology used. 

So.... I have a column in my data, "workflow start" and I am trying to get a visual that will show a count of all items that have a start date within a certain month. Something I feel should be easy is causing me so many problems. 

I tried the =today formula as below, with success, but I worry that if the data I have loaded only goes up to end of October (as it will be issued monthly) anyone viewing the report in Novemeber will get false results?

WF Start - Current Month = IF(YEAR([Workflow Start])=YEAR(TODAY()), IF(MONTH([Workflow Start])=MONTH(TODAY()),"Issued"))

The column is set to "Date" format (see screenshot below with example extract of data)

All suggestions welcome 🙂

Many Thanks

Nicola

[v-stephen-msft · Accepted solution]

Hi @nr83 ,

The following is my solution, please point out if it is not right.

1.Create a calculated table by New table under Calculations. Make sure that the new table is not related to the old table.

Slicer = 
DISTINCT (
    SELECTCOLUMNS (
        FILTER ( 'Table', [Workflow Start] <> BLANK () ),
        "YearMonth",
            YEAR ( [Workflow Start] ) & "-"
                & FORMAT ( MONTH ( [Workflow Start] ), "00" )
    )
)

2.Create a measure to count the YearMonth which is selected.

Count YearMonth =
CALCULATE (
    COUNT ( 'Table'[Workflow Start] ),
    FILTER (
        'Table',
        MONTH ( 'Table'[Workflow Start] )
            = VALUE ( RIGHT ( SELECTEDVALUE ( 'Slicer'[YearMonth] ), 2 ) )
            && YEAR ( 'Table'[Workflow Start] )
                = VALUE ( LEFT ( SELECTEDVALUE ( 'Slicer'[YearMonth] ), 4 ) )
    )
)

3.The result is this.

You can check more details from here.

Best Regards,

Stephen Tao

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

[amitchandak]

@nr83 , if this is your regular date format you can use this setting

https://community.powerbi.com/t5/Desktop/How-to-apply-UK-date-format-dd-mm-yyyy-in-Date-slicer/td-p/...

Or create a date like

new date =
var _pos = search("/",[col],,0)
return
date(right([date],4), mid([date],_pos+1,2), left([date],_pos-1))

Now you can analyze with this date or join with date table, have a month on the date tbale amd analzye count

Count(Table[Date])

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.