Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
BMM27
Helper I
Helper I

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!
1 ACCEPTED SOLUTION

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!

View solution in original post

7 REPLIES 7
lbendlin
Super User
Super User

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

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



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

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/1447...

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 

lbendlin_0-1715634677647.png

 

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!

Solved!

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors