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!
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

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

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)
RETURN
    CALCULATE(
        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 

    • BMM27's avatar
      BMM27
      Icon for Helper I rankHelper I
      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

        Solved!

        Daily Budget = SUMX(VALUES(Dim_Calendar[Date]), (CALCULATE([Budget], STARTOFMONTH(Dim_Calendar[Date]))) / DAY(EOMONTH(MIN(Dim_Calendar[Date]), 0))) * [SelectedDays]