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: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
rutealvesdc
Frequent Visitor

Repeating an information when blank but within groups

‎10-06-2021 01:47 PM

I have a database of purchased items and their prices. But, on some months, there's no purchase of some items. The idea is to repeat the price of the last purchase until we buy the item again. This repetion shouldn't be limited. I've tried using the DAX code below, but I only get the value to repeat for one time. I must note that the variable Price is a column created directly on the table in Data View. Can someone help?

 

  • Within a group of items
  • Repeat the previous data of price if there wasn't a purchase on the current month
  • Repeat until the next purchase

 

Current code:

 

Pre_Value =
   VAR ItemGroup = 'Table'[Item]
   VAR Date1 = 'Table'[Month-Year]
   VAR Pre_Date = PREVIOUSMONTH('Table'[Month-Year])
   VAR Prev_Val = CALCULATE(MAX('Table'[Price]),FILTER('Table','Table'[Item]=ItemGroup),FILTER('Table','Table'[Month-Year]=Pre_Date))
RETURN IF('Table'[Price]=BLANK(),Prev_Val,'Table'[Price])

 

Outcome example:

 

ItemPriceMonth-YearPre_Value
100519 2021-10 
100793 2021-06 
100793952021-0795
100793672021-0867
100793 2021-0967
100793 2021-10 

 

Expected outcome:

 

ItemPriceMonth-YearPre_Value
100519 2021-10 
100793 2021-06 
100793952021-0795
100793672021-0867
100793 2021-0967
100793 2021-1067
Message 1 of 10
1,149 Views
0
1 ACCEPTED SOLUTION
smpa01
Super User
Super User

‎11-09-2021 07:10 AM

@rutealvesdc  find the attached pbix. It has the desired result in both calculated column and measure

_fdColumn = 
VAR _0 =
    CALCULATE (
        LASTNONBLANK ( t1[Date], 1 ),
        FILTER (
            ALL ( t1 ),
            t1[Date] <= EARLIER ( t1[Date] )
                && t1[Item] = EARLIER ( t1[Item] )
                && NOT ( ISBLANK ( t1[Price] ) )
        )
    )
VAR _1 =
    CALCULATE ( SUM ( t1[Price] ), FILTER ( ALL ( t1 ), t1[Date] = _0 ) )
RETURN
    _1
_fdMeasure = 
VAR _mxDate =
    MAX ( dt[Date] )
VAR _2 =
    CALCULATE (
        MAX ( dt[Date] ),
        FILTER ( ALL ( dt ), dt[Date] <= _mxDate && [_Price] <> 0 )
    )
VAR _3 =
    CALCULATE ( [_Price], ALL ( dt ), dt[Date] = _2 )
RETURN
    _3

 

smpa01_0-1636470591377.png

 

 

smpa01_1-1636470619552.png

 

 

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

View solution in original post

_fd.pbix
Message 7 of 10
939 Views
0
9 REPLIES 9
smpa01
Super User
Super User

‎11-09-2021 07:10 AM

@rutealvesdc  find the attached pbix. It has the desired result in both calculated column and measure

_fdColumn = 
VAR _0 =
    CALCULATE (
        LASTNONBLANK ( t1[Date], 1 ),
        FILTER (
            ALL ( t1 ),
            t1[Date] <= EARLIER ( t1[Date] )
                && t1[Item] = EARLIER ( t1[Item] )
                && NOT ( ISBLANK ( t1[Price] ) )
        )
    )
VAR _1 =
    CALCULATE ( SUM ( t1[Price] ), FILTER ( ALL ( t1 ), t1[Date] = _0 ) )
RETURN
    _1
_fdMeasure = 
VAR _mxDate =
    MAX ( dt[Date] )
VAR _2 =
    CALCULATE (
        MAX ( dt[Date] ),
        FILTER ( ALL ( dt ), dt[Date] <= _mxDate && [_Price] <> 0 )
    )
VAR _3 =
    CALCULATE ( [_Price], ALL ( dt ), dt[Date] = _2 )
RETURN
    _3

 

smpa01_0-1636470591377.png

 

 

smpa01_1-1636470619552.png

 

 

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
_fd.pbix
Message 7 of 10
940 Views
0
vkoppunuru
Frequent Visitor
In response to smpa01

‎03-07-2023 10:40 AM

Thanks smpa01.

This worked for me.

Message 10 of 10
670 Views
0
rutealvesdc
Frequent Visitor
In response to smpa01

‎11-09-2021 08:59 AM

Thank you!

Message 8 of 10
920 Views
0
smpa01
Super User
Super User
In response to rutealvesdc

‎11-10-2021 04:45 AM

@rutealvesdc did you try the solution yet? If yes, and it works please accept the solution.

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
Message 9 of 10
877 Views
0
BeaBF
Super User
Super User

‎10-07-2021 04:57 AM

@rutealvesdc 

Can you send the table on which create the correct measure?

 

Thx,

B.

Message 2 of 10
1,124 Views
0
rutealvesdc
Frequent Visitor
In response to BeaBF

‎10-07-2021 06:07 AM

Yes!
How can I share it with you? Can I send it to your e-mail?

Message 3 of 10
1,106 Views
0
BeaBF
Super User
Super User
In response to rutealvesdc

‎10-07-2021 06:14 AM

@rutealvesdc  you can attack here in a comment your table.

 

Thx,

B.

Message 4 of 10
1,091 Views
0
rutealvesdc
Frequent Visitor
In response to BeaBF

‎11-08-2021 05:42 AM

Hi! Do you have any suggestion?

Message 6 of 10
1,012 Views
0
rutealvesdc
Frequent Visitor
In response to BeaBF

‎10-07-2021 06:46 AM
Item-Year-MonthPriceItemYearMonthMonth-YearPre_Value
10030420211 100304202112021-01 
10030420212 100304202122021-02 
10030420213 100304202132021-03 
10030420214 100304202142021-04 
10030420215 100304202152021-05 
10030420216 100304202162021-06 
1003042021763,7226891100304202172021-0763,7226891
1003042021857,5616458100304202182021-0857,5616458
10030420219 100304202192021-0957,5616458
100304202110 1003042021102021-10 
100304202111 1003042021112021-11 
100304202112 1003042021122021-12 
10030420221 100304202212022-01 
10030420222 100304202222022-02 
10030420223 100304202232022-03 
10030420224 100304202242022-04 
10030420225 100304202252022-05 
10030420226 100304202262022-06 
10030420227 100304202272022-07 
10030420228 100304202282022-08 
10030420229 100304202292022-09 
100304202210 1003042022102022-10 
100304202211 1003042022112022-11 
100304202212 1003042022122022-12 
10051920211 100519202112021-01 
10051920212 100519202122021-02 
10051920213 100519202132021-03 
10051920214 100519202142021-04 
10051920215 100519202152021-05 
10051920216 100519202162021-06 
10051920217 100519202172021-07 
10051920218606,03100519202182021-08606,03
10051920219 100519202192021-09606,03
100519202110 1005192021102021-10 
100519202111 1005192021112021-11 
100519202112 1005192021122021-12 
10051920221 100519202212022-01 
10051920222 100519202222022-02 
10051920223 100519202232022-03 
10051920224 100519202242022-04 
10051920225 100519202252022-05 
10051920226 100519202262022-06 
10051920227 100519202272022-07 
10051920228 100519202282022-08 
10051920229 100519202292022-09 
100519202210 1005192022102022-10 
100519202211 1005192022112022-11 
100519202212 1005192022122022-12 
10079320211 100793202112021-01 
10079320212 100793202122021-02 
10079320213 100793202132021-03 
10079320214 100793202142021-04 
10079320215 100793202152021-05 
10079320216 100793202162021-06 
1007932021795100793202172021-0795
1007932021867100793202182021-0867
10079320219 100793202192021-0967
100793202110 1007932021102021-10 
100793202111 1007932021112021-11 
100793202112 1007932021122021-12 
10079320221 100793202212022-01 
10079320222 100793202222022-02 
10079320223 100793202232022-03 
10079320224 100793202242022-04 
10079320225 100793202252022-05 
10079320226 100793202262022-06 
10079320227 100793202272022-07 
10079320228 100793202282022-08 
10079320229 100793202292022-09 
100793202210 1007932022102022-10 
100793202211 1007932022112022-11 
100793202212 1007932022122022-12 
Message 5 of 10
1,070 Views
0

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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

View All