Show 0 for current month without data

DAX DAX Calculations
1

image
Particle sum =
VAR A =
SUM ( ‘Particle_Raw data’[数量] )
VAR B =
EOMONTH ( TODAY (), 0 )
VAR C =
MIN ( ‘Particle_Raw data’[Date日期] )
VAR D =
IF ( C < B , A, blank() )
RETURN
D

Would like to check whether is it possible from as shown on the left in the picture like the picture on the right.

Aug 22 without any data. So it will not show 0 on Aug 22 but the data shown until July 22. Any idea on DAX calculation?

Thank you

2

Hello @dennistgc,

Thank You for posting your query onto the Forum.

In order to show the value as “0” for current month. You’ll be required to have a date table inside your data model and then write the measure something like this -

Particle sum =
VAR _Total_Sales =
SUM ( ‘Particle_Raw data’[数量] )

VAR _Current_Month_Start_Date =
DATE( YEAR( TODAY() ) , MONTH( TODAY() ) , 1 )

VAR _Current_Month_End_Date =
DATE( YEAR( TODAY() ) , MONTH( TODAY() ) , DAY( TODAY() ) )

VAR _Results = 
SWITCH( TRUE() , 
    MIN( Dates[Date] ) < _Current_Month_Start_Date , _Total_Sales ,
    MIN( Dates[Date] ) >= _Current_Month_Start_Date && MIN( Dates[Date] ) <= _Current_Month_End_Date , 0 , 
BLANK() )

RETURN
_Results

I’m also attaching the working of the PBIX file for the reference purposes wherein I’ve re-created the scenario that you’ve suggested.

Hoping you find this useful and meets your requirements that you’ve been looking for.

Thanks and Warm Regards,
Harsh

Show 0 For Current Month Without Data - Harsh.pbix (64.8 KB)

1 Like
3

Thanks for the reply. What if I want to add that for Sep to to Dec show blank but it still can show in the table? Appreciate your kind assistance.
TQ

4

Hello @dennistgc,

Then add one more line as a condition inside the “SWITCH( TRUE() )” statement -

Particle sum =
VAR _Total_Sales =
SUM ( ‘Particle_Raw data’[数量] )

VAR _Current_Month_Start_Date =
DATE( YEAR( TODAY() ) , MONTH( TODAY() ) , 1 )

VAR _Current_Month_End_Date =
DATE( YEAR( TODAY() ) , MONTH( TODAY() ) , DAY( TODAY() ) )

VAR _Results = 
SWITCH( TRUE() , 
    MIN( Dates[Date] ) < _Current_Month_Start_Date , _Total_Sales ,
    MIN( Dates[Date] ) >= _Current_Month_Start_Date && MIN( Dates[Date] ) <= _Current_Month_End_Date , 0 , 
    MIN( Dates[Date] ) > _Current_Month_End_Date , 0 , 
BLANK() )

RETURN
_Results

Thanks and Warm Regards,
Harsh

Show 0 For Current Month Without Data - Harsh v2.pbix (64.7 KB)

1 Like
5

VAR _Results =

SWITCH( TRUE() ,

MIN( Dates[Date] ) < _Current_Month_Start_Date , _Total_Sales ,

MIN( Dates[Date] ) >= _Current_Month_Start_Date && MIN( Dates[Date] ) <= _Current_Month_End_Date , 0 ,

MIN( Dates[Date] ) > _Current_Month_End_Date && MIN( Dates[Date] ) <= _Current_Year_End_Date, BLANK() ,

BLANK() )

I am trying to make Sep 22 to Dec 22 as blank but show in the visual. Can you please help to check the switch condition. As I still cannot get the result. TQ

6

Hello @dennistgc,

Please provide the PBIX file so that members of the Forum can assist you in a better and efficient manner. It’s difficult to provide assistance just based on a piece of code without looking at the data structure which in turn involves a lot of guess work and therefore consumes the time of the Forum members as well as increases the length of the thread.

If your data contains any senstitive information than create a mock-up file by sanitizing the data. Below is the link of the blog as well as video provided for the reference about how to mask the sensitive data.

Thanks and Warm Regards,
Harsh

1 Like
7

Hi @dennistgc, possible to send your working pbix so the community can help you better on your query? Thanks

8

Hello @dennistgc, we’ve noticed that no response has been received from you since Aug 16th.

We waited for your masked demo pbix file. Other users can help you more if you provide the PBIX file in your query.

Due to your inactivity, we’ll be tagging this post as Solved.

Should you wish to add your masked demo pbix file, you can reopen this thread.