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

[avulasandeep]

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 .

Advance Thanks 

Thanks 

sandeep

[Ashish_Mathur]

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.

[avulasandeep]

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

[Ashish_Mathur · Accepted solution]

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.

[ajin]

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 

[Ashish_Mathur]

Hi,

Yes.  Just select one date in the date slicer.

[JAVED]

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

[NipponSahore]

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. 

[avulasandeep]

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

[NipponSahore]

@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