Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Pandadev
Post Prodigy
Post Prodigy

Cumulative values not working , just showing the total value for year for each month

‎01-08-2024 09:18 AM

I have a line chart , which has 2 measures that i want to show the cumulative totals for the selected year.

One is the actual cost and the other is the budget.

The data is from 3 different tables .

I have a YearTable , with column named Year , it has yeras 2022,2023,2024

Year

Month

Date_ = DATE(VALUE('BudgetTable_CoS'[Year]), MONTH(DATEVALUE('BudgetTable_CoS'[Month] & " 1")), 1)
 
Data Cost List
 
This measure works correctly 
Cumulative_Cost =
VAR SelectedYear = SELECTEDVALUE('YearTable'[Year], YEAR(TODAY()))
VAR FirstMonthDate = CALCULATE(MIN('Data Costs List'[Date]), FILTER(ALL('Data Costs List'), YEAR('Data Costs List'[Date]) = SelectedYear))
VAR LastMonthDate = CALCULATE(MAX('Data Costs List'[Date]), FILTER(ALL('Data Costs List'), YEAR('Data Costs List'[Date]) = SelectedYear))
VAR CurrentDate = MAX('Data Costs List'[Date])
VAR EffectiveEndDate = IF(CurrentDate < LastMonthDate, CurrentDate, LastMonthDate)
RETURN
IF(
    NOT(ISBLANK(FirstMonthDate)) && NOT(ISBLANK(LastMonthDate)),
    CALCULATE(
        SUM('Data Costs List'[Value]),
        FILTER(
            ALL('Data Costs List'),
            'Data Costs List'[Date] >= FirstMonthDate &&
            'Data Costs List'[Date] <= EffectiveEndDate
        )
    ),
    BLANK()
)
 
But this measure shows just the totals of the 12 months , for each month , so if the monthly totals for 2022 were , 100 , then each month is showing 1200 , when it should be Jan 100 , feb 200 , mar 300 etc
 
Measure is 
 
Cumulative_Budget =
VAR SelectedYear = SELECTEDVALUE('YearTable'[Year], YEAR(TODAY()))
RETURN
CALCULATE(
    SUM('BudgetTable_CoS'[Month Total]),
    FILTER(
        ALL('BudgetTable_CoS'),
        YEAR('BudgetTable_CoS'[Date_]) = SelectedYear &&
        'BudgetTable_CoS'[Date_] <= MAX('BudgetTable_CoS'[Date_])
    )
)
 
The x axis on the line chart is from the Data Cost List table , column named Month_Year , which is like Jan-22
 
I have tried
 
Cumulative_Budget = VAR SelectedYear = SELECTEDVALUE('YearTable'[Year], YEAR(TODAY())) VAR CumulativeTotalByMonth = SUMMARIZE( FILTER( ALL('BudgetTable_CoS'), YEAR('BudgetTable_CoS'[Date_]) = SelectedYear && 'BudgetTable_CoS'[Date_] <= MAX('BudgetTable_CoS'[Date_]) ), 'BudgetTable_CoS'[Date_], "CumulativeTotal", CALCULATE(SUM('BudgetTable_CoS'[Month Total])) ) RETURN SUMX(CumulativeTotalByMonth, [CumulativeTotal])
 
and
 
Cumulative_Budget = VAR SelectedYear = SELECTEDVALUE('YearTable'[Year], YEAR(TODAY())) VAR CurrentDate = MAX('BudgetTable_CoS'[Date_]) RETURN CALCULATE( SUM('BudgetTable_CoS'[Month Total]), FILTER( ALL('BudgetTable_CoS'), YEAR('BudgetTable_CoS'[Date_]) = SelectedYear && 'BudgetTable_CoS'[Date_] <= CurrentDate ) )
 
but I get the same result each time
 
 
 
 
 

 

 

Labels:
Message 1 of 3
129 Views
0
2 REPLIES 2
VijayP
Super User
Super User

‎01-08-2024 08:58 PM

@Pandadev 

This is due to datapreparation , you are complicating the result.

First you must have single source of truth with date dimension.

if you have sample, it will be easy to explain with just 2 Measures!




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!


Message 2 of 3
85 Views
0
Pandadev
Post Prodigy
Post Prodigy
In response to VijayP

‎01-09-2024 04:22 AM

I have a table named Budget_Table , which has columns

Date_ a date column 1st of month year

Month Total , which is the monthly total budget

Month_ which is Jan,Feb etc

Month_Year ordered by Date , format mmm yyyy , Jan 2022

Type_Month_Year , this is used to filter the Data Cost List table , with same named column , it has relationship set up

My measure for cumulative cost is working 

Cumulative_Cost =
VAR CurrentDate = MAX('Data Costs List'[Date])
VAR StartYear = SELECTEDVALUE('YearTable'[Year])
RETURN
CALCULATE(
    SUM('Data Costs List'[Value]),
    FILTER(
        ALL('Data Costs List'[Date]),
        'Data Costs List'[Date] >= StartYear &&
        'Data Costs List'[Date] <= CurrentDate
    ),
    ALLSELECTED('Data Costs List')
)
This one is not working , any ideas why
 
Cumulative_Budget =
VAR SelectedYear = SELECTEDVALUE('YearTable'[Year])
VAR SelectedTypeMonthYearCombo = SELECTEDVALUE('Data Costs List'[Type_Month_Year_Combo])
VAR CumulativeTotal =
    CALCULATE(
        SUMX(
            FILTER(
                ALL('Budget_Table'),
                YEAR('Budget_Table'[Date_]) = SelectedYear &&
                'Budget_Table'[Type_Month_Year_Combo] = SelectedTypeMonthYearCombo &&
                'Budget_Table'[Date_] <= MAX('Budget_Table'[Date_])
            ),
            'Budget_Table'[Month Total]
        )
    )
RETURN
CumulativeTotal
here is example table 
here is the table data for Budget_Table , if year 2023 was selected and the Data Cost & Royalties selected , then the cumulitive result should , Jan 200 , Feb 400 , Mar 600 , Apr 800 , May 1000 , Jun 1200 , Jul 1400 , Aug 1600 , Sep 1800 , Oct 2000 , Nov 2200 , Dec 2400 or if year 2022 was selected and the Data Cost & Royalties selected , then the cumulitive result should , Jan 100 , Feb 200 , Mar 300 , Apr 400 , May 500 , Jun 600 , Jul 700 , Aug 800 , Sep 900 , Oct 1000 , Nov 1100 , Dec 1200 - table Month_ Year Month_Year Type_Month_Year_Combo Month Total Date_
Apr 2022 Apr-22 Data Cost & RoyaltiesApr2022 100 01/04/2022
Apr 2023 Apr-23 Data Cost & RoyaltiesApr2023 200 01/04/2023
Aug 2022 Aug-22 Data Cost & RoyaltiesAug2022 100 01/08/2022
Aug 2023 Aug-23 Data Cost & RoyaltiesAug2023 200 01/08/2023
Dec 2022 Dec-22 Data Cost & RoyaltiesDec2022 100 01/12/2022
Dec 2023 Dec-23 Data Cost & RoyaltiesDec2023 200 01/12/2023
Feb 2022 Feb-22 Data Cost & RoyaltiesFeb2022 100 01/02/2022
Feb 2023 Feb-23 Data Cost & RoyaltiesFeb2023 200 01/02/2023
Jan 2022 Jan-22 Data Cost & RoyaltiesJan2022 100 01/01/2022
Jan 2023 Jan-23 Data Cost & RoyaltiesJan2023 200 01/01/2023
Jul 2022 Jul-22 Data Cost & RoyaltiesJul2022 100 01/07/2022
Jul 2023 Jul-23 Data Cost & RoyaltiesJul2023 200 01/07/2023
Jun 2022 Jun-22 Data Cost & RoyaltiesJun2022 100 01/06/2022
Jun 2023 Jun-23 Data Cost & RoyaltiesJun2023 200 01/06/2023
Mar 2022 Mar-22 Data Cost & RoyaltiesMar2022 100 01/03/2022
Mar 2023 Mar-23 Data Cost & RoyaltiesMar2023 200 01/03/2023
May 2022 May-22 Data Cost & RoyaltiesMay2022 100 01/05/2022
May 2023 May-23 Data Cost & RoyaltiesMay2023 200 01/05/2023
Nov 2022 Nov-22 Data Cost & RoyaltiesNov2022 100 01/11/2022
Nov 2023 Nov-23 Data Cost & RoyaltiesNov2023 200 01/11/2023
Oct 2022 Oct-22 Data Cost & RoyaltiesOct2022 100 01/10/2022
Oct 2023 Oct-23 Data Cost & RoyaltiesOct2023 200 01/10/2023
Sep 2022 Sep-22 Data Cost & RoyaltiesSep2022 100 01/09/2022
Sep 2023 Sep-23 Data Cost & RoyaltiesSep2023 200 01/09/2023
Apr 2022 Apr-22 Other costsApr2022 40 01/04/2022
Apr 2023 Apr-23 Other costsApr2023 50 01/04/2023
Aug 2022 Aug-22 Other costsAug2022 40 01/08/2022
Aug 2023 Aug-23 Other costsAug2023 50 01/08/2023
Dec 2022 Dec-22 Other costsDec2022 40 01/12/2022
Dec 2023 Dec-23 Other costsDec2023 50 01/12/2023
Feb 2022 Feb-22 Other costsFeb2022 40 01/02/2022
Feb 2023 Feb-23 Other costsFeb2023 50 01/02/2023
Jan 2022 Jan-22 Other costsJan2022 40 01/01/2022
Jan 2023 Jan-23 Other costsJan2023 50 01/01/2023
Jul 2022 Jul-22 Other costsJul2022 40 01/07/2022
Jul 2023 Jul-23 Other costsJul2023 50 01/07/2023
Jun 2022 Jun-22 Other costsJun2022 40 01/06/2022
Jun 2023 Jun-23 Other costsJun2023 50 01/06/2023
Mar 2022 Mar-22 Other costsMar2022 40 01/03/2022
Mar 2023 Mar-23 Other costsMar2023 50 01/03/2023
May 2022 May-22 Other costsMay2022 40 01/05/2022
May 2023 May-23 Other costsMay2023 50 01/05/2023
Nov 2022 Nov-22 Other costsNov2022 40 01/11/2022
Nov 2023 Nov-23 Other costsNov2023 50 01/11/2023
Oct 2022 Oct-22 Other costsOct2022 40 01/10/2022
Oct 2023 Oct-23 Other costsOct2023 50 01/10/2023
Sep 2022 Sep-22 Other costsSep2022 40 01/09/2022
Sep 2023 Sep-23 Other costsSep2023 50 01/09/2023
Apr 2022 Apr-22 Software CostsApr2022 100 01/04/2022
Apr 2023 Apr-23 Software CostsApr2023 1000 01/04/2023
Aug 2022 Aug-22 Software CostsAug2022 100 01/08/2022
Aug 2023 Aug-23 Software CostsAug2023 1000 01/08/2023
Dec 2022 Dec-22 Software CostsDec2022 100 01/12/2022
Dec 2023 Dec-23 Software CostsDec2023 1000 01/12/2023
Feb 2022 Feb-22 Software CostsFeb2022 100 01/02/2022
Feb 2023 Feb-23 Software CostsFeb2023 1000 01/02/2023
Jan 2022 Jan-22 Software CostsJan2022 100 01/01/2022
Jan 2023 Jan-23 Software CostsJan2023 1000 01/01/2023
Jul 2022 Jul-22 Software CostsJul2022 100 01/07/2022
Jul 2023 Jul-23 Software CostsJul2023 1000 01/07/2023
Jun 2022 Jun-22 Software CostsJun2022 100 01/06/2022
Jun 2023 Jun-23 Software CostsJun2023 1000 01/06/2023
Mar 2022 Mar-22 Software CostsMar2022 100 01/03/2022
Mar 2023 Mar-23 Software CostsMar2023 1000 01/03/2023
May 2022 May-22 Software CostsMay2022 100 01/05/2022
May 2023 May-23 Software CostsMay2023 1000 01/05/2023
Nov 2022 Nov-22 Software CostsNov2022 100 01/11/2022
Nov 2023 Nov-23 Software CostsNov2023 1000 01/11/2023
Oct 2022 Oct-22 Software CostsOct2022 100 01/10/2022
Oct 2023 Oct-23 Software CostsOct2023 1000 01/10/2023
Sep 2022 Sep-22 Software CostsSep2022 100 01/09/2022
Sep 2023 Sep-23 Software CostsSep2023 1000 01/09/2023
Apr 2022 Apr-22 Telephone.Apr2022 10 01/04/2022
Apr 2023 Apr-23 Telephone.Apr2023 20 01/04/2023
Aug 2022 Aug-22 Telephone.Aug2022 10 01/08/2022
Aug 2023 Aug-23 Telephone.Aug2023 20 01/08/2023
Dec 2022 Dec-22 Telephone.Dec2022 10 01/12/2022
Dec 2023 Dec-23 Telephone.Dec2023 20 01/12/2023
Feb 2022 Feb-22 Telephone.Feb2022 10 01/02/2022
Feb 2023 Feb-23 Telephone.Feb2023 20 01/02/2023
Jan 2022 Jan-22 Telephone.Jan2022 10 01/01/2022
Jan 2023 Jan-23 Telephone.Jan2023 20 01/01/2023
Jul 2022 Jul-22 Telephone.Jul2022 10 01/07/2022
Jul 2023 Jul-23 Telephone.Jul2023 20 01/07/2023
Jun 2022 Jun-22 Telephone.Jun2022 10 01/06/2022
Jun 2023 Jun-23 Telephone.Jun2023 20 01/06/2023
Mar 2022 Mar-22 Telephone.Mar2022 10 01/03/2022
Mar 2023 Mar-23 Telephone.Mar2023 20 01/03/2023
May 2022 May-22 Telephone.May2022 10 01/05/2022
May 2023 May-23 Telephone.May2023 20 01/05/2023
Nov 2022 Nov-22 Telephone.Nov2022 10 01/11/2022
Nov 2023 Nov-23 Telephone.Nov2023 20 01/11/2023
Oct 2022 Oct-22 Telephone.Oct2022 10 01/10/2022
Oct 2023 Oct-23 Telephone.Oct2023 20 01/10/2023
Sep 2022 Sep-22 Telephone.Sep2022 10 01/09/2022
Sep 2023 Sep-23 Telephone.Sep2023 20 01/09/2023
Apr 2022 Apr-22 Travel & SubsistenceApr2022 400 01/04/2022
Apr 2023 Apr-23 Travel & SubsistenceApr2023 500 01/04/2023
Aug 2022 Aug-22 Travel & SubsistenceAug2022 400 01/08/2022
Aug 2023 Aug-23 Travel & SubsistenceAug2023 500 01/08/2023
Dec 2022 Dec-22 Travel & SubsistenceDec2022 400 01/12/2022
Dec 2023 Dec-23 Travel & SubsistenceDec2023 500 01/12/2023
Feb 2022 Feb-22 Travel & SubsistenceFeb2022 400 01/02/2022
Feb 2023 Feb-23 Travel & SubsistenceFeb2023 500 01/02/2023
Jan 2022 Jan-22 Travel & SubsistenceJan2022 400 01/01/2022
Jan 2023 Jan-23 Travel & SubsistenceJan2023 500 01/01/2023
Jul 2022 Jul-22 Travel & SubsistenceJul2022 400 01/07/2022
Jul 2023 Jul-23 Travel & SubsistenceJul2023 500 01/07/2023
Jun 2022 Jun-22 Travel & SubsistenceJun2022 400 01/06/2022
Jun 2023 Jun-23 Travel & SubsistenceJun2023 500 01/06/2023
Mar 2022 Mar-22 Travel & SubsistenceMar2022 400 01/03/2022
Mar 2023 Mar-23 Travel & SubsistenceMar2023 500 01/03/2023
May 2022 May-22 Travel & SubsistenceMay2022 400 01/05/2022
May 2023 May-23 Travel & SubsistenceMay2023 500 01/05/2023
Nov 2022 Nov-22 Travel & SubsistenceNov2022 400 01/11/2022
Nov 2023 Nov-23 Travel & SubsistenceNov2023 500 01/11/2023
Oct 2022 Oct-22 Travel & SubsistenceOct2022 400 01/10/2022
Oct 2023 Oct-23 Travel & SubsistenceOct2023 500 01/10/2023
Sep 2022 Sep-22 Travel & SubsistenceSep2022 400 01/09/2022
Sep 2023 Sep-23 Travel & SubsistenceSep2023 500 01/09/2023
Message 3 of 3
72 Views
0

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.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

View All