cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

Display the Data , if Date is less than 15th of the Month and Greater than 15th of the Month

HI Experts , 

 

here i  need dax function :

 

if Date is Greater than 15th of this Month i need to display the data of all the month's data till last month and Less than 15th of the Month means i need to display all the month data except last month .

latest dax function.JPG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

any one know this formula or method  let me know .

 

Advance Thanks 

 

Thanks 

sandeep

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Display the Data , if Date is less than 15th of the Month and Greater than 15th of the Month

Hi,

 

Try this

 

  1. Create a Calendar Table
  2. Create a relationship from the Date column of the Data Table to the Date column of the Calendar Table
  3. Create a slicer visual and drag the Date from the Calendar table to the slicer
  4. Select any one date in the slicer visual
  5. Enter this measure

=CALCULATE(SUM(Data[Sales]),DATESBETWEEN(Calendar[Date],DATE(YEAR(MIN(Calendar[Date])),1,1),IF(DAY(MIN(Calendar[Date]))<15,EOMONTH(MIN(Calendar[Date]),-2),EOMONTH(MIN(Calendar[Date]),-1))))

 

Hope this helps.

9 REPLIES 9

Re: Display the Data , if Date is less than 15th of the Month and Greater than 15th of the Month

Please confirm if the Date you're mentioning is the max date in your dataset or do you need a column which cumulatively sums up the sale for for every record. 

Re: Display the Data , if Date is less than 15th of the Month and Greater than 15th of the Month

Yes Date is the define as the Max date and sales is cummulative sum with respect to given condition ....

If the date is <15 of the month ...data has to display all the months cummulative sum from starting month of the year to till date except last Month

If the date >15 of the month ...data has to display all the months cummulative sum from starting month of the year to till date including last month cummulative sum also .

Thanks
Sandeep
Super User
Super User

Re: Display the Data , if Date is less than 15th of the Month and Greater than 15th of the Month

Hi,

 

Could you show your expected result.  In a simple table, what do you want to see in the row labels/column labels and in the value area section.

Re: Display the Data , if Date is less than 15th of the Month and Greater than 15th of the Month

@avulasandeep

 

you could use something like

DAX = 
var MaxDate = max(date)
var startdate = Date(Year(maxDate),"01","01")
return 
if (day(maxDate)<=15, 
Calculate(sum(sales), filter(table,datebetween(date,startdate,EOMONTH(maxDate,-2)))),
Calculate(sum(sales), filter(table,datebetween(date,startdate,EOMONTH(maxDate,-1))))
)


Assuming you want the sum of only 1 year. You can change this by setting the StartDate.
Also assumed for the date of 15th of every month you want the result to be the same as 14th.

 

Let me know if that works out

Re: Display the Data , if Date is less than 15th of the Month and Greater than 15th of the Month

Hi 
My Output is like  in below  pic.

 

 

new req 31 may.JPG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

this is the output of the  i want to display ie  

if i selected date is march 14th then output should be the sum(sales) for the month of January.

if i selected date is march 16th then output should be the sum(sales) for the month of January+ Sum(sales) for month of february.

 

 

 

Thanks 

sandeep

Super User
Super User

Re: Display the Data , if Date is less than 15th of the Month and Greater than 15th of the Month

Hi,

 

Try this

 

  1. Create a Calendar Table
  2. Create a relationship from the Date column of the Data Table to the Date column of the Calendar Table
  3. Create a slicer visual and drag the Date from the Calendar table to the slicer
  4. Select any one date in the slicer visual
  5. Enter this measure

=CALCULATE(SUM(Data[Sales]),DATESBETWEEN(Calendar[Date],DATE(YEAR(MIN(Calendar[Date])),1,1),IF(DAY(MIN(Calendar[Date]))<15,EOMONTH(MIN(Calendar[Date]),-2),EOMONTH(MIN(Calendar[Date]),-1))))

 

Hope this helps.

JAVED Regular Visitor
Regular Visitor

Re: Display the Data , if Date is less than 15th of the Month and Greater than 15th of the Month

hi!

i too have the same problem but not to display between the date need to display the sale of start date as one column and end date sales as one column

Highlighted
ajin Regular Visitor
Regular Visitor

Re: Display the Data , if Date is less than 15th of the Month and Greater than 15th of the Month

Hi @Ashish_Mathur,

 

With reference to your post, could you tell me what point number 4 and 5 are saying? . I guessing 5 is to create a measure. But i wasnt clear with 4. Does that mean we just select a value in list date slicer.

 

Thanks

Aj 

Super User
Super User

Re: Display the Data , if Date is less than 15th of the Month and Greater than 15th of the Month

Hi,

 

Yes.  Just select one date in the date slicer.

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 6 members 2,583 guests
Please welcome our newest community members: