Set start date for a series in a line chart

[imranamikhan]

Hi everyone,

I have a Data table which is refreshed monthly. The Data table has a Date field joined to a generic Date table.

My Data table has four cumulative counts. Cumulative Raised, Cumulative Closed, Cumulative Pending, and Cumulative Pending + Cumulative Closed.

I have added Cumulative Raised & Cumulative Closed to a line graph.

I want to add a third cumulative count of “Cumulative Pending + Cumulative Closed”.

However, I want this count to only start in the next month - but the current max Date in my Data table is Dec 2020.

Is there a way to achieve this? I am suspecting I will need a second Date table.

I have to put together an example in Excel. If anyone has any ideas to share that would be appreciated.

https://1drv.ms/x/s!AkH_PeScw-iuh4EEQsPy8nF4AS07ww?e=ETeYhj

Best regards,

[amitchandak]

@imranamikhan , Try a measure like this with original columns and date table

New measure =
var _max = maxx(allselected(Data), Data[Date])
return
if(Max(Date[Date] >= _max , calculate(Sumx(Date,[Count Pending] + [Count Closed]), Filter(Allselected('Date'), 'Date'[Date] <= Max('Date'[Date]))), blank())

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.

[imranamikhan]

Hi @amitchandak  - seeing an error at the Date part:

[amitchandak]

@imranamikhan , that should be data. Both data and date are so similar, My mistake

New measure =
var _max = maxx(allselected(Data), Data[Date])
return
if(Max(Date[Date] >= _max , calculate(Sumx(Data,[Count Pending] + [Count Closed]), Filter(Allselected('Date'), 'Date'[Date] <= Max('Date'[Date]))), blank())

[imranamikhan]

Thanks @amitchandak. A different error this time. I suspect this is less to do with the DAX and more to do with how my data is setup? I have messaged you a link to my file if you ever have time to have a look.

[amitchandak]

@imranamikhan , Shared one formula back on PM

[imranamikhan]

@parry2k @amitchandak. That is the expected output. Perhaps it is a poor example. You will notice "Closed" flatlines in January, but Cumulative Pending + Closed increases in January (circled in red).

For more context - Each item in my data has an associated Status (Raised, Closed, Pending). The dates in my Data end at December. 

By the end of December, we had x number of items Raised, and x number of items Closed.

I have created two measures, cumulative items Raised, and cumulative items Closed. I have added these to a Line Chart.

However, in my Data, there are some Items we know will be closed in the following month (January) and these have a status of Pending. I want to be able to include these items as a series in the Line chart, but the problem is that I do not have Dates in my Data which go beyond December.

I hope that is clearer but happy to clarify more.

[amitchandak]

@imranamikhan , when Data table do have data after Dec -2020, how are you plan to show Cumulative Pending + Cumulative Closed in Jan, are you trying show dec data in Jan

In such a case you have measure like

if(isblank(max(Date[Date])) , calculate([Cumulative Pending] + [Cumulative Closed], previousmonth('Date'[Date])), blank())

[parry2k]

@imranamikhan not sure what you mean start from next month? What exactly you are looking for? In your example, you showed how it is laid down but what is the expected output.