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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Syndicate_Admin
Administrator
Administrator

Get previous month's sales of products that have sales on the current date

Hello community, I always guide myself from their publications but it is the first time I look for and look for and I do not find if they can help me with this problem that has come out:

As the title says I want to filter in some way the sales of the previous month that I get with this formula:

Advance LM < =
CALCULATE([Advance],DATEADD(Calendar[Date],-1, MONTH))

And only show sales from the previous month when they have sales in the current period. Some told me but condition if it is blank then do not put anything, if that can work when it is separated by category. But when they are grouped, the conditional does not work:
Epuma_1-1633039294708.png


I am using as an example only one day I leave you a test pbix that I put together to try to perform the measurement.

Epuma_0-1633039118837.png
PBix :
https://we.tl/t-UzhXXDMTBG

@Greg_Deckler

1 ACCEPTED SOLUTION

Hi,

Write this measure

Measure = SUMX(FILTER(VALUES('data pruebas'[DISTRIBUIDOR]),[Avance]>0),[Avance LM <])

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

8 REPLIES 8
AllisonKennedy
Super User
Super User

@Syndicate_Admin 

 

I'm not sure how you wish to define 'current period' but if just generic across the whole model, then you need to provide that current period context by iterating on the Calendario table. Iterator functions end in X usually, such as SUMX. 

 

I believe this formula is what you're looking for, but if not just reply with what the desired result should be: 

 

Avance LM < AK =
SUMX(Calendario,
IF([Avance]<>BLANK(),
CALCULATE([Avance],DATEADD(Calendario[Date],-1, MONTH))
)
)
 
AllisonKennedy_0-1633048903435.png

 

Note, this does not filter per Distribuidor each date period, but rather aggregates them all, so if that's something you need, we need to know EVERY dimension you want included and we need to add that to the context of the iterator measure. 


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Ashish_Mathur
Super User
Super User

Hi,

Try this measure

Avance LM < = 
if(ISBLANK([Avance]),BLANK(),CALCULATE([Avance],DATEADD(Calendario[Date],-1, MONTH)))

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

As I tell you it is true that it serves when it is blank, but when they are grouped it no longer respects the condition the correct result should only be: 59947

1.JPG

Hi,

Write this measure

Measure = SUMX(FILTER(VALUES('data pruebas'[DISTRIBUIDOR]),[Avance]>0),[Avance LM <])

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hello I worked well at the level records this formula but to show every day is not adding correctly the value, I was thinking of using a hasonevalue but it would not be the solution since this measure will be used in many ways and you would have to create many hasonvalue :

Epuma_0-1633106611696.png




@Epuma  

 

Which measure are you using for this screenshot? Have you tried my suggestion using the SUMX(Calendario ... this will provide the context for the totals row you're looking for.

 


@Syndicate_Admin wrote:

Hello I worked well at the level records this formula but to show every day is not adding correctly the value, I was thinking of using a hasonevalue but it would not be the solution since this measure will be used in many ways and you would have to create many hasonvalue :

 





 

 


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Hi,

Share the link from where i can download your PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

WAO that great if it works on the PBix test tomorrow I will try it on the original PBix that weighs almost 900 MB thank you very much !

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

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! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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