Duplicating data for the first day of the month to the whole month

[Franklin1]

Hey smart people,

I have a super tough issue here, and i would kindly appreciate any kind of help.

I am trying to solve this issue with no success for too much time.

I have a set of queries with a lot of data, and they are all linked and work great.

The issue is that one of the tables, include data (with links to other tables - such as date, segment) but instead of having the data (#) for the whole month, it is only mention the number for the first day of the month. 

The number is OK, but should be the same for the whole month.

instead, I have the right number gor the 1st day of the month, and 0 for the rest of the month.

i addition, i take this number as the denominator for a few calculations that i am doing, and again, the result is OK for the 1st day of the month, but the rest of the month it is Infinity...

I really need your help here, and appreciate any idea!!!

I am running out of ideas at this stage, and this job has to be done urgently...

thanks a lot for any support!

[cs_skit]

if thats possible for you to fix it on the data level you might be able to use this in PowerQuery (aka Query Editor)

[Franklin1]

Thanks a lot, but the table have no rows for the other days, but only for the 1st day of each month.

Do you think of any other solution?

[Franklin1]

Maybe a measure I can use, to mention that whether the date is differnet than the 1st of the day of the month, the return of the data should be the same as the data for the 1st of the day of the month.

Anyone knows the code for this measure?

Thanks. 

[cs_skit]

Is the data table where you only have one row per month for the first day really "date specific" data or is it more month specific data?

Because if its the latter you should join it to the month of your Calendar table instead of the specific date. Then for all dates of that month you could reference the data with RELATED() and don't have the issue of empty fields.

[Franklin1]

Thanks for your reply.

The table is date specific...

can you elaborate more about the RELATED() idea.

Maybe i can use it anyway?

[v-qiuyu-msft · Accepted solution]

Hi @Franklin1,

Assume the sample data start from 2016-6-1 like below:

Then you can create a calendar table in Query Editor by click Get Data -> Blank Query, type = List.Dates(#date(2016, 6, 1), 500, #duration(1, 0, 0, 0)) in formula bar. Then convert list to a table.

Use Merge Queries as New feature, Fill-> Down, then get below data.

For details, you can download attached DuplicateData.pbix file to have a look.

Best Regards,
Qiuyun Yu

[cs_skit]

I create custom time intelligence

http://databear.com/2016/11/08/power-bi-tip-dynamic-calendar-table/

then in my Calendar table I create all kinds of additional useful fields

There is also first day of the month in there which you could reference to your seperate table

but its usually better to reference year and Month like 201706. 

so you could extract the data to this form into a seperate table and then link it

Month	SomeValues
201704	500
201705	600
201706	750