Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Rolling 12 month total

I have some data which shows the date a plan was issued and whether it was issued on time or late (data below) - at the moment the data has plans issued back to Jan 2018 but this may be extended late...
Need Help
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi Anonymous ,

You can create the measures as below:

Issued on Time = CALCULATE(DISTINCTCOUNT('Table'[ID]),'Table'[On Time?]="Issued on Time")
Issued on Time(R12) = CALCULATE([Issued on Time], DATESBETWEEN (
        'Calendar'[Date],
        NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( 'Calendar'[Date] ) ) ),
        LASTDATE ( 'Calendar'[Date] )
    ) )
Issued Late = CALCULATE(DISTINCTCOUNT('Table'[ID]),'Table'[On Time?]="Issued Late")
Issued Late(R12) = CALCULATE([Issued Late],DATESBETWEEN (
        'Calendar'[Date],
        NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( 'Calendar'[Date] ) ) ),
        LASTDATE ( 'Calendar'[Date] )
    ) )
Issue on Time % = DIVIDE( [Issued on Time],([Issued on Time]+[Issued Late]))
Issue on Time(R12) % = DIVIDE( [Issued on Time(R12)],([Issued on Time(R12)]+[Issued Late(R12)]))

I just created a sample pbix file, you can get it from this link.

Best Regards

Rena

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi Rena

 

That is really helpful for the most part - couple of issues which don't quite seem to be working right which I can't quite work out how to fix...

 

In your example, it is picking up data from 2017 even though the data set only goes back to Jan 2018.

 

In my version of it, it is not doing that for most graphs but it is for the comb-graph for issued on time in month and over a rolling 12 it is pulling in all the dates in my date calendar, even when a specific date is selected - all of the other graphs are filtering fine - any ideas?

 

Thanks 🙂

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous ,

    Sorry for replying late. Could you please give me an example to tell which calculated values are incorrect and what should be the correct values? Also, where does the date field applied on slicer come from? If it is convenient, could you please provide your pbix file directly in order to make troubleshooting based on your actual scenario?

    Best Regards

    Rena