Forum Discussion

BMM27's avatar
BMM27
Icon for Helper I rankHelper I
2 years ago
Solved

Measure Monthly to diary.

Hi, I have a measure [A] where it gives total monthly value. Additionally, I have a measure [B] that calculates the daily value by dividing [A] by the total number of days in the month. However, rega...
@measure
@NeedHelp
needhelp
  • BMM27's avatar
    BMM27
    2 years ago

    Hi, here is my solution, but It causes anther question:
    My count selected days measure:

    SelectedDays=
    VAR Month= MONTH(MAX(Dim_Calendar[Date]))
    VAR y = YEAR(MAX(Dim_Calendar[Date]))
    VAR SelectedDate=
        FILTER(
            ALL(Dim_Calendar),
            Dim_Calendar[Date] >= MIN(Dim_Calendar[Date]) &&
            Dim_Calendar[Date] <= MAX(Dim_Calendar[Date]) &&
            MONTH(Dim_Calendar[Date]) = Month&&
            YEAR(Dim_Calendar[Date]) = y
        )
    RETURN
        COUNTROWS(SelectedDate)
     
    Daily Budget = (CALCULATE([Budget], STARTOFMONTH(Dim_Calendar[Date])) / DAY(EOMONTH(MIN(Dim_Calendar[Date]), 0))) * [SelectedDays]

    What happens is if I put this on a table, the row of totals is wrong, same as if I put on a Card. Only when the data is with each month selected the data is perfectly correct. 
    With a Card, maybe, I understand it a llittle but on a table, the totals it have to sum all the values but they don't.

    I know this is because it's made with a measure, but I think this is only the solution.

    If anyone knows how to solve this, I would appreciate it, thanks!
BMM27's avatar
BMM27
Icon for Helper I rankHelper I
2 years ago

I'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, 

dailyPrice = DIVIDE(CALCULATE(SUM(myTable[TotalMonthPrice]), myTable[sector] = "A"), DAY(EOMONTH(myTable[Date], 0)))

Next, a measure that counts number of days selected in the month of the record.

DaysCount = VAR month= MONTH(MAXX(myTable, myTable[Date]))
VAR year= YEAR(MAXX(myTable, myTable[Date]))
VAR min= FIRSTDATE(Calendar[Date])
VAR max= LASTDATE(Calendar[Date])
VAR selected= FILTER('Calendar',
                                'Calendar'[Date] >= min&&
                                'Calendar'[Date] <= max&&
                                MONTH('Calendar'[Date]) = month &&
                                YEAR('Calendar'[Date]) = year)
RETURN
    CALCULATE(
        COUNTROWS(selected),
        ALL(myTable),
        myTable[Date] = MAX('Calendar'[Date])
    )

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.
PriceACC = SUM(myTable[dailyPrice]) * [DaysCount]

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-202401-2024A1R33A31010
01-01-202401-2024A2T44B29029
01-02-202402-2024A3T33C29029
01-02-202402-2024B1R44D29029
01-03-202403-2024C2T33E31031
01-03-202403-2024A3R44F31031



lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
2 years ago

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