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, regardless of the month selected, [B] always returns the value for the last month.
What I want is a new measure that provides the sum of [B] for the selected date range.
Here are the different measures.

A =IF (
                [IsValid],
                CALCULATE ( SUM ( 'table'[valueA] ), table[valueB] = "C" ),
                BLANK () )
    )

B = 
VAR MonthlyValue = [A]
VAR SelectedMonth = SELECTEDVALUE('Calendar'[Month])
VAR SelectedYear = SELECTEDVALUE('Calendar'[Year])
VAR DaysInMonth = DAY(EOMONTH(DATE(SelectedYear, SelectedMonth, 1), 0))
RETURN
    IF(
        [IsValid],
        MonthlyValue / DaysInMonth,
        BLANK()
    )

Example with data.

For example, if I select the date range from February 1, 2024, to April 5, 2024, I expect the result to be:

If the daily value of feb is 5, march is 6 and apr is 7, the result would be 5*days in February(29)+6*days in March(31)+7*days in April(5 in that case). The result is 145+186+35=366

 

Any assistance would be greatly appreciated. Thank you!



Any help would be good, thanks!
@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!

7 Replies

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

    Add a proper Calendar table to your data model and use a row count over that instead - much simpler.

    • 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