topic Display START DATE of month for last 6 months when any date is selected in slicer in DAX Commands and Tips

Display START DATE of month for last 6 months when any date is selected in slicer

Anonymous — Wed, 08 Dec 2021 05:05:32 GMT

Hello Team,

START date of the month should display for last 6 months rows when any date is selected in slicer

ex:

Slicer: sep 2021, oct 202, nov 2021

Expected output:

Selected Nov 2021 in slicer

OP: 11/1/2021 for last 6 months rows

Selected Oct 2021 in slicer

OP: 10/1/2021 for last 6 months rows

Selected Sep 2021 in slicer

OP: 9/1/2021 for last 6 months rows

Re: Display START DATE of month for last 6 months when any date is selected in slicer

amitchandak — Wed, 08 Dec 2021 05:38:58 GMT

Anonymous , if you select a date and want data more than that, you need an independent date slicer

Check this

Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI

Re: Display START DATE of month for last 6 months when any date is selected in slicer

Anonymous — Wed, 08 Dec 2021 06:36:42 GMT

Hello amitchandak I have gone through the above video, that is not working for my scenario...

if i create MEASURE for displaying STARTING date of the month when date is selected in slicer

then i have to use another measure for another type of calculation....if i use date range slicer then it wont work....

below is the spotfire results.... i have selected SEP 2021 month in slicer of spotfire then its returning 9/1/2021 for last 6 months rows... i required this dax. could you please help me out on this..

Re: Display START DATE of month for last 6 months when any date is selected in slicer

amitchandak — Wed, 08 Dec 2021 07:34:06 GMT

Anonymous , Assume month coming from table have date then you can start of month like

eomonth(Maxx(allselected('Date'), 'Date'[Date]) ,-1)+1

rolling 6 month measure

Rolling 6 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-6,MONTH))

but prefer to use date table

Re: Display START DATE of month for last 6 months when any date is selected in slicer

Anonymous — Wed, 08 Dec 2021 09:23:02 GMT

amitchandak , still getting error for max function is accepts column but not rows..can we connect on this for 10mins...its urgent requirement

Re: Display START DATE of month for last 6 months when any date is selected in slicer

VahidDM — Wed, 08 Dec 2021 10:38:03 GMT

Hi Anonymous

Can you post sample data as text and expected output?
It's not that clear at least for me!

please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
4. Relation between your tables

Appreciate your Kudos!!
LinkedIn:www.linkedin.com/in/vahid-dm/

Re: Display START DATE of month for last 6 months when any date is selected in slicer

Anonymous — Wed, 08 Dec 2021 15:23:35 GMT

its working as amitchandak suggested to me like eomonth(maxx(date),-1)+1)...im getting expected results