Show 13 months for a chosen year in a diagram

[Anonymous]

I am counting the number of vehicles at the end of each month.

In my DimDate table I have (among other columns) Year (text), Year Month (text) and Date (Date) and OnlyLastDayofMonth (ABC/123) with only the last date of each month in it.

Each vehicle is included in all dates in a range from start date to end date.

My measure:

Vehicle Count end of month = calculate( DISTINCTCOUNT('TestObject/Transactions'[test_object_id]),DimDate[OnlyLastDayofMonth]<>"")

I need to have the total per the last day of December as my opening balance for every selected year.

So when I choose the year 2021, I want to see Dec 2020-Dec 2021. At the moment I am abviously only getting Jan-Dec.

 How can I get 12 months + the last month of the preceeding year as the first visible month? I.e. 13 months instead of 12?

Thanks, Anna.

[v-xiaotang]

Hi @Anonymous 

Please try this measure

Vehicle Count end of month =
VAR _End =
    DATE ( MIN ( 'DimDate'[Year] ), 12, 31 )
VAR _start =
    EOMONTH ( _End, -13 )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'TestObject/Transactions'[test_object_id] ),
        FILTER (
            'TestObject/Transactions',
            'TestObject/Transactions'[date] > _start
                && 'TestObject/Transactions'[date] <= _End
        )
    )

Best Regards,

Community Support Team _Tang

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

[Anonymous]

Thank you! Brilliant thinking! @v-xiaotang 

The thing is, I am counting vehicled that exist beteen dates (Delivery date and end date). So I have an interval table and then I count the vehices at the end of each month.

Whe I used yoyr brilliant measure (changed a bit):

Community Vehicle Count end of month =

VAR _End =

DATE ( MIN ( 'DimDate'[Year] ), 12, 31 )

VAR _start =

EOMONTH ( _End, -13 )

RETURN

CALCULATE (

DISTINCTCOUNT ( 'TestObject/Transactions'[test_object_id] ),

FILTER (

'DimDate',

'DimDate'[OnlyLastDayofMonth] > _start &&

'DimDate'[OnlyLastDayofMonth] <= _End

)

)

I still have the problem that when I filter per year, I am only getting the count for that year.  I would for example like to include YearMonth 202112 when I filter per year 2022...

[v-xiaotang · Accepted solution]

Hi @Anonymous 

Thanks for your reply. 

The reason for the error is because you use the year from dimdate as the slicer, so the visual will be filtered.

(1) In this scenario, you need to create a year table for slicer, 

(2) then try this, create the measure below,

Vehicle Count end of month = 
VAR _End =
    DATE ( SELECTEDVALUE(YearSlicer[Year]), 12, 31 )
VAR _start =
    EOMONTH ( _End, -13 )
return IF(MIN(DimDate[Date])<_End,
     CALCULATE (
        DISTINCTCOUNT ( 'TestObject/Transactions'[test_object_id] ),
        FILTER (
            'TestObject/Transactions',
            'TestObject/Transactions'[date] > _start
                && 'TestObject/Transactions'[date] < EOMONTH( MIN(DimDate[Date]),0)
        )
    ) ,BLANK())

then it returns the distinctcount of [test_object_id] at the end of each month

Best Regards,

Community Support Team _Tang

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

[Ashish_Mathur]

Hi,

See if my post here helps - Flex a Pivot Table to show data for x months ended a certain user defined month.

[Anonymous]

No sorry 😞