Forum Discussion
Helper IMeasure Monthly to diary.
Super UserAdd a proper Calendar table to your data model and use a row count over that instead - much simpler.
Helper II'm unsure about what you are referring to.
Here's what I currently have done:
I created a column in calendar and in myTable with mm-yyyy dates. And related both.
Then I created another column in myTable, it calculates daily price by month,
Next, a measure that counts number of days selected in the month of the record.
Finally, I created a measure that gives me the daily sum from selected dates. However, if I select dates between two months, it always gives me the value of last month selected instead of the sum of all.
And always from day 1 to selected day, If i select 05-01-2024 to 10-01-2024 It gives me the value of 01-01-2024 to 10-01-2024 instead of 05 to 10.
If i group by sector for example and I select dates between two different months, I want to calculate the sum of dailyPrice for each month.
For instance if I select from 20-01-2024 to 5-02-2024 I expect to multiply the sum of dailyPrice on january by 11 and on february by 5. However, my current measure, is only multiplying by 5 (last selected month).
Here's an example table:
| Date | MM-YYYY | country | shop | sector | category | employee | totalMonthPrice | dailyPrice |
| 01-01-2024 | 01-2024 | A | 1 | R | 33 | A | 310 | 10 |
| 01-01-2024 | 01-2024 | A | 2 | T | 44 | B | 290 | 29 |
| 01-02-2024 | 02-2024 | A | 3 | T | 33 | C | 290 | 29 |
| 01-02-2024 | 02-2024 | B | 1 | R | 44 | D | 290 | 29 |
| 01-03-2024 | 03-2024 | C | 2 | T | 33 | E | 310 | 31 |
| 01-03-2024 | 03-2024 | A | 3 | R | 44 | F | 310 | 31 |
- lbendlin
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information or anything not related to the issue or question.
If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523- BMM27
Hi, It was a table, here I attached a .xlsx with some data.
It is the result of multiplying 'SaleDaily' by 'DaysSelected'
The column SalesAcc is what I expect. When visualizing the data in a chart grouped by a certain key, 'SalesAcc' equals the sum of those values. Is the result of multiplying SaleDaily with DaysSelected. Take care, If i group by Key1, what I expect with SalesAcc is sum each value of different month and days month.
SaleDaily equals to TotalMonthSale Divided with number of days of its corresponding month, in that case January or February.
DaysSelected is a measure that calculates the number of selected days for each month. I'm filtering from 02/01/2023 to 08/02/2023.DaysSelected= VAR Month = MONTH(MAXX(Table, Table[Date]))VAR Year= YEAR(MAXX(Table, Table[Date]))VAR Datemin= FIRSTDATE(Calendar[Date])VAR Datemax = LASTDATE(Calendar[Date])VAR Selected= FILTER('Calendar','Calendar'[Date] >= Datemin&&'Calendar'[Date] <= Datemax&&MONTH('Calendar'[Date]) = Month&&YEAR('Calendar'[Date]) = Year)RETURNCALCULATE(COUNTROWS(Selected),ALL(Table),Table[Date] = MAX('Calendar'[Date]))SaleDaily= DIVIDE(CALCULATE(SUM(Table[TotalMonthSales]), Table[Key6] = "RRR"), DAY(EOMONTH(Table[Date], 0)))I hope this information is suficient for your undersanding. Link To Data
- lbendlin