How to display all dates in a month on the x-axis whether data set contains all the dates or not?

[gnandhu]

Hi All,

Here is the situation,

Application    UsageDate     Price

-------------------------------------

App1              2017-03-01    10

App1              2017-03-02    12

App1              2017-03-03    15 

App1              2017-03-04    10  

App1              2017-03-05    12 

App2              2017-03-01    15 

App2              2017-03-02    14 

App2              2017-03-03    12 

App2              2017-03-04    11 

App2              2017-03-05    10

A table contains two application with date of usage and the cost, here max date of usgae for both application are 2017-03-05,

I want to plot a line in Line Chart that shows estimation for complete month. So the line should start from 2017-03-06 to 2017-03-31.

An the cost is : PreviousDayPrice * Remaining days in current month

For App1 Future Cost is : PreviousDayPrice [12] * Remaining days in current month[26] = 312 

For App2 Future Cost is : PreviousDayPrice [10] * Remaining days in current month[26] = 260

Can any one advise please?

Thanks,

[v-sihou-msft · Accepted solution]

@gnandhu

We can't have each app show the last non empty price in the rest of dates in current month. In this scenario, we have to generate a full calendar table, then crossjoin Apps with prices in the first 5 days. Then create a calculated column to populate the last non empty price into blank cells.

1. Create a calcendar table.

Calendar = CALENDAR("2017/3/1","2017/3/31")

2. Create a calculated table to have the calendar table CROSSJOIN Apps with existing price populated.

Table = ADDCOLUMNS(CROSSJOIN(SUMMARIZE(Apps,Apps[App]),'Calendar'),"price",LOOKUPVALUE(Apps[Price],Apps[App],Apps[App],Apps[Date],'Calendar'[Date]))

3. Add a calculated column to get the last non empty date.

LastDate = CALCULATE(MAX(Apps[Date]),ALLEXCEPT(Apps,Apps[App],Apps[Year],Apps[Month])) 

4. Add another calculatedd column the LOOKUPVALUE the corresponding Price.

Updated Price = IF('Table'[price]=BLANK(),LOOKUPVALUE(Apps[Price],Apps[App],'Table'[App],Apps[Date],'Table'[LastDate]),'Table'[price])

Regards,