Forum Discussion

Pall's avatar
Pall
Regular Visitor
2 years ago
Solved

How to calculate Cumulative Values when there are missing dates ?

Hi Sir,
Using line graph to display cumulative values
using below DAX. to handle missing dates and also have slicers to filter required sub_products.
**bleep** Mat Sold  =
Var _cum =CALCULATE(SUM ( Table Name [Sales] ),
'Date Table'[Date] <= MAX('Date Table'[Date]))
Return
IF(ISBLANK(SUM ( Table Name [Sales] )),_cum+0,_cum)
Example :
Date Prod Sub_Prod Sales
1/1/2022 sam sam_2 10
1/2/2022 Ram Ram_1 2
1/3/2022 sam sam_2 4
1/4/2022 Sam Sam_1 0
1/5/2022 sam sam_2 8
1/6/2022 Ram Ram_2 3
1/7/2022 Ram Ram_1 5
1/8/2022 Tem Tem_1 11
1/9/2022 Sam Sam_1 0

As you see in below table .If filter by Sub_PRod -> the graph line is going down at missing dates. Actually the graph must should have straight line at missing dates

Date Prod Sub_Prod Sales
1/1/2022 sam sam_2 10
1/3/2022 sam sam_2 4
1/4/2022 Sam Sam_1 0
1/5/2022 sam sam_2 8
1/9/2022 Sam Sam_1 0

Please help me for the solution to handle for missing dates values
Thanks in advance

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Thanks for the reply from Ashish_Mathur and Uzi2019 , please allow me to provide another insight:

    Hi  Pall ,

     

    Here are the steps you can follow：

    1. Create calculated table.

    Table 2 =
DISTINCT('Table'[Sub_Prod])

    2. Create measure.

    Sales_Measure =
var _select=SELECTCOLUMNS('Table 2',"test",'Table 2'[Sub_Prod])
return
IF(
    MAX('Table'[Sub_Prod]) in _select,SUM('Table'[Sales]),0)

    If you want to do all accumulation based on the slicer selection, you can use the following measure

    Cumulative Sales =
SUMX(
    FILTER(ALL('Table'),
    'Table'[Date]<=MAX('Table'[Date])),[Sales_Measure])

    If you want to do year and month grouping accumulation based on the slicer selection, you can use the following measure

    Cumulative Sales Group =
SUMX(
    FILTER(ALL('Table'),    YEAR('Table'[Date])=YEAR(MAX('Table'[Date]))&&MONTH('Table'[Date])=MONTH(MAX('Table'[Date]))&&'Table'[Date]<=MAX('Table'[Date])),[Sales_Measure])

    3. Result:

     

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

6 Replies

  • Uzi2019's avatar
    Uzi2019
    Icon for Community Champion rankCommunity Champion
    2 years ago

    Hi Pall 
    Try below dax

    Cumulative = 
  VAR __Date = MAX('DIM_Date'[Date])
  VAR __Table = FILTER(ALL('Table'), [Date] <= __Date)
  VAR __Result = SUMX( __Table, [Column])
RETURN
  __Result

     

    I hope i would resolved your issue!

     

     

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

    Hi,

    Not sure whether those 2 tables are raw data tables or not.  Just share 2 tables - input and result.  Also, specify the filter condition for generating the result table.  Share data in a format that can be pasted in an MS Excel file.

    • Pall's avatar
      Pall
      Regular Visitor
      2 years ago

      For suppose this is actual table with T_Sales

      DateProdSub_ProdSales
      1/1/2022samsam_210
      1/2/2022RamRam_12
      1/3/2022samsam_24
      1/4/2022SamSam_10
      1/5/2022samsam_28
      1/6/2022RamRam_23
      1/7/2022RamRam_15
      1/8/2022TemTem_111
      1/9/2022SamSam_10

      Need to calculate cumulative Sales 
      and Result should 

      DateProdSub_ProdSalesCumulative Sales
      1/1/2022samsam_21010
      1/2/2022RamRam_1212
      1/3/2022samsam_2416
      1/4/2022SamSam_1016
      1/5/2022samsam_2824
      1/6/2022RamRam_2327
      1/7/2022RamRam_1532
      1/8/2022TemTem_11143
      1/9/2022SamSam_1043

       
      And filter data by using Slicer Sub_Prod -> Sam_1 and Sam_2 
       Result should be 

      DateProdSub_ProdSalesCumulative Sales
      1/1/2022samsam_21010
      1/2/2022RamRam_1010
      1/3/2022samsam_2414
      1/4/2022SamSam_1014
      1/5/2022samsam_2822
      1/6/2022RamRam_2022
      1/7/2022RamRam_1022
      1/8/2022TemTem_1022
      1/9/2022SamSam_1022

       

      If i use same "cumulative sales " line graph the line should have all the dates with +0 vlaues 

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Thanks for the reply from Ashish_Mathur and Uzi2019 , please allow me to provide another insight:

    Hi  Pall ,

     

    Here are the steps you can follow：

    1. Create calculated table.

    Table 2 =
DISTINCT('Table'[Sub_Prod])

    2. Create measure.

    Sales_Measure =
var _select=SELECTCOLUMNS('Table 2',"test",'Table 2'[Sub_Prod])
return
IF(
    MAX('Table'[Sub_Prod]) in _select,SUM('Table'[Sales]),0)

    If you want to do all accumulation based on the slicer selection, you can use the following measure

    Cumulative Sales =
SUMX(
    FILTER(ALL('Table'),
    'Table'[Date]<=MAX('Table'[Date])),[Sales_Measure])

    If you want to do year and month grouping accumulation based on the slicer selection, you can use the following measure

    Cumulative Sales Group =
SUMX(
    FILTER(ALL('Table'),    YEAR('Table'[Date])=YEAR(MAX('Table'[Date]))&&MONTH('Table'[Date])=MONTH(MAX('Table'[Date]))&&'Table'[Date]<=MAX('Table'[Date])),[Sales_Measure])

    3. Result:

     

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly