Forum Discussion

D1ltang's avatar
D1ltang
Frequent Visitor
8 years ago

calculate dates between two measures

Hi I have 2 simple measure. One takes the selections on the slicers into account and creates a date. The other uses this newly created date to work out the 1st of the month a year ago. These both wor...
Need Help
Abduvali's avatar
Abduvali
Skilled Sharer
8 years ago

Hi D1ltang,

 

Don't know if you tried the following if not give it a try this might do a trick for you:

 

Create new Calendar table and create an active  Many-to-One relationship on dates with your Working table. then use your new Date from Calendar table in your table visual.

 

DAX for Calendar table:

Calendar=
ADDCOLUMNS (
CALENDAR (DATE(2016,1,1), DATE(2017,12,31)),
"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
"Year", YEAR ( [Date] ),
"Monthnumber", FORMAT ( [Date], "MM" ),
"YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),
"YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),
"MonthNameShort", FORMAT ( [Date], "mmm" ),
"MonthNameLong", FORMAT ( [Date], "mmmm" ),
"DayOfWeekNumber", WEEKDAY ( [Date] ),
"DayOfWeek", FORMAT ( [Date], "dddd" ),
"DayOfWeekShort", FORMAT ( [Date], "ddd" ),
"Quarter", "Q" & FORMAT ( [Date], "Q" ),
"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" )
)

 

 

Hope this helps.

 

 

Regards

Abduvali

D1ltang's avatar
D1ltang
Frequent Visitor
8 years ago

Hi Abduvali

 

Thank you for taking the time to respond.

 

Unfortunately the start and end dates that I am using are not static. They are generated as a result of the users input via slicer selection. This solution would be perfect if I could pass my two measures as the Start and End Date in your calendar calculation.

 

Have you got any other suggestions?

 

The final Visual is what I am trying to achieve

 

Thanks again,

 

Dil

  • Abduvali's avatar
    Abduvali
    Skilled Sharer
    8 years ago

    D1ltang

     

     

    Can you make a better and bigger screenshot :) cant see anything on this one and please include a screenshot of your table with a Date column.

     

    Thanks

  • D1ltang's avatar
    D1ltang
    Frequent Visitor
    8 years ago

     

    Hopefully that is clearer and has what you would like to see.

     

    Thanks,

     

    Dil