Dividing daily data by monthly data

[Anonymous]

Hi guys,

I have 2 data sets 

Set A Period (Daily)	Value
01/01/20	5
02/02/20	6
03/02/20	7
04/02/20	8
05/02/20	9

Set B period (Monthly)	Value
01/01/20 - Jan	70
01/02/20 - Feb	50
01/03/20 - Mar	60
01/04/20 - Apr	50

I need the individual days division in which for Set A / Set B daily
01/01/20 - 5 / 70

02/01/20 - 6 / 70 
03/01/20 - 7 / 70

Now, I am just getting start of the month data because it is taking set B period 01/01/20 not as the month of janurary but for the literal date of 01/01/20 giving me the graph.

I believe the main idea would to make 01/01/20 in the month data set B be repeated for everyday in the same month = 02/01/20 = 03/01/20 ... = 70 but i have no idea how to to do that 

[v-janeyg-msft · Accepted solution]

Hi, @Anonymous 

It’s my pleasure to answer for you.

According to your description, I think you can create a measure to calculate your desired result.

Like this:

result =
VAR a =
    SELECTEDVALUE ( Table1[Value] )
VAR b =
    CALCULATE (
        SUM ( Table2[Value] ),
        FILTER (
            ALL ( Table2 ),
            FORMAT ( [Set B period (Monthly)], "mmm yyyy" )
                = FORMAT ( SELECTEDVALUE ( Table1[Set A Period (Daily)] ), "mmm yyyy" )
        )
    )
RETURN
    DIVIDE ( a, b )

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , Create a common date table and join both tables to date table date usinf date column  and then group the data at month level using month in date table

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

divide(Sum(Table1[Daily(Value]) ,Sum(Table2[Monthly(Value]) )

Appreciate your Kudos.

[Anonymous]

Hi Amit, I dont really understand the part of grouping it by month.

So far i've done this, by my data output gives me 

@amitchandak