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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
DanielLinda
Helper IV
Helper IV

Get Previous Running Total for Missing Month

Hi All,

 

I want to make accumulative line graph of which line should always increase.

- Even though there is missing month for each item, the month should exist with previous value.

so the graph must not decrease or cut for the missing month.

Let say I have two tables like below, the 'Date' table and 'ShipQty' table has relation in date column.

X axis: Date, Y axis: Qty

 

image.png

 

image.png

 

 

image.png

 

 

Please kindly help me.

 

Thanks in advance.

 

 

1 ACCEPTED SOLUTION
v-alq-msft
Community Support
Community Support

Hi, @DanielLinda 

 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

ShipQty:

f1.png

 

Date:

Date = CALENDAR(DATE(2001,1,1),DATE(2022,12,31))

 

There is a relationship between two tables.

 

You may create  calculated columns and a measure as below.

Calculated column:
YearMonth = VALUE(FORMAT('Date'[Date],"yyyymm"))
YearMonthDay = FORMAT('Date'[Date],"yyyy-mm-"&"01")
YearMonth2 = VALUE(FORMAT(ShipQty[Date],"yyyymm"))

Measure:
Result = 
CALCULATE(
    SUM(ShipQty[Qty]),
    FILTER(
        ALL(ShipQty),
        ShipQty[Item] = SELECTEDVALUE(ShipQty[Item])&&
        ShipQty[YearMonth2]<=SELECTEDVALUE('Date'[YearMonth])
    )
)

 

Result:

f2.png

 

Best Regards

Allan

 

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

View solution in original post

2 REPLIES 2
v-alq-msft
Community Support
Community Support

Hi, @DanielLinda 

 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

ShipQty:

f1.png

 

Date:

Date = CALENDAR(DATE(2001,1,1),DATE(2022,12,31))

 

There is a relationship between two tables.

 

You may create  calculated columns and a measure as below.

Calculated column:
YearMonth = VALUE(FORMAT('Date'[Date],"yyyymm"))
YearMonthDay = FORMAT('Date'[Date],"yyyy-mm-"&"01")
YearMonth2 = VALUE(FORMAT(ShipQty[Date],"yyyymm"))

Measure:
Result = 
CALCULATE(
    SUM(ShipQty[Qty]),
    FILTER(
        ALL(ShipQty),
        ShipQty[Item] = SELECTEDVALUE(ShipQty[Item])&&
        ShipQty[YearMonth2]<=SELECTEDVALUE('Date'[YearMonth])
    )
)

 

Result:

f2.png

 

Best Regards

Allan

 

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

Greg_Deckler
Super User
Super User

Right, so normally the way you tackle this is to have a separate, (sometimes unrelated) calendar table with all the dates. You use the date column from this in your visualization and code your measure like this:

 

Cumulative Measure =
  VAR __Date = MAX('Calendar'[Date])
RETURN
  SUMX(FILTER('Table','Table'[Date] <= __Date),[Column])

 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.