Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
hi everyone!
please help with the following,
I'm trying to convert montly target value for sales into daily average with respect to number of days in a given month.
this is my data model
tables are connected through calendar table,
Actual table has data on daily level, but Plan table has data on monthly level
and I need to see it in the following format
important thing to concider is days in each month
Solved! Go to Solution.
@Ali_Shakh
Please try
Planned Sales Daily =
DIVIDE (
[Planned Sales],
COUNTROWS (
CALCULATETABLE (
VALUES ( 'Calendar'[Date] ),
REMOVEFILTERS (),
VALUES ( 'Calendar'[Year] ),
VALUES ( 'Calendar'[Month] )
)
)
)
Hi @Ali_Shakh
why did you divide the march planned amount over 46.5 days? 331,962/46.5 = 7,139?
hi @tamerj1 ,
sorry, my bad I captured more days for march,
daily figure should be 10 708
@Ali_Shakh
Please try
Planned Sales Daily =
DIVIDE (
[Planned Sales],
COUNTROWS (
CALCULATETABLE (
VALUES ( 'Calendar'[Date] ),
REMOVEFILTERS (),
VALUES ( 'Calendar'[Year] ),
VALUES ( 'Calendar'[Month] )
)
)
)
@Ali_Shakh
This is a relationship cardinality issue.
You need to create the relationship between 'Date' and 'Planned' at month level not at day level. You can create a YearMonth column like FORMAT ( Planned[Date], "YYYYMM" ) in the Planned table and FORMAT ( 'Date'[Date], "YYYYMM" ) in the 'Date" table and connect both columns together Many-Many single direction.
User | Count |
---|---|
47 | |
26 | |
22 | |
17 | |
15 |
User | Count |
---|---|
55 | |
34 | |
18 | |
17 | |
15 |