cancel
Showing results for
Did you mean:
Member

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 .

any one know this formula or method  let me know .

Thanks

sandeep

1 ACCEPTED SOLUTION

Accepted Solutions
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
Member

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.

Member

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

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.

Member

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

Member

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.

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

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.

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
Regular Visitor

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

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

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.

Announcements

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.

Power Platform Summit North America

Register by September 5 to save \$200

PBI Community Highlights

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

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 6 members 2,583 guests
Recent signins: