cancel
Showing results for
Did you mean:
Frequent Visitor

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

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

Thanks,

1 ACCEPTED SOLUTION
Microsoft

@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,

Microsoft

@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,