Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
gnandhu
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

 

Can any one advise please?

 

Thanks,

 

 

1 ACCEPTED SOLUTION
v-sihou-msft
Employee
Employee

@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])

gg.PNG

 

 

Regards,

View solution in original post

1 REPLY 1
v-sihou-msft
Employee
Employee

@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])

gg.PNG

 

 

Regards,

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.