Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Kmur
Regular Visitor

YEAR TO GO AND HALF YEAR TO GO

Hi,

 

I´m trying to create a DAX that would show my total sales in future. So if I select May 18 I would like to see sales total from June 18-Dec 18.  Same with half year. 

Haven´t found any good tips from community by far. Does anyone has a good tip how to do this?

 

Thanks in advance!

 

 

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

Hi,

 

Here's a start

 

=CALCULATE(SUM(Data[Sales]),DATESBETWEEN(Calendar[Date],MAX(Calendar[Date])+1,DATE(YEAR(MAX(Calendar[Date])),12,31)))

 

There should be a Calendar Table and an active relatiosnhip from the Date column of your source data Table to the Date column of your Calendar Table.  In the Calendar Table, write these calculated column formuals to extract the Month and Year

 

=YEAR(Calendar[Date])

=FORMAT(Calendar[Date],"mmmm")

 

In the Filter/slicer, select a certain Year and Month which you dragged from the Calendar Table.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

7 REPLIES 7
Ashish_Mathur
Super User
Super User

Hi,

 

Here's a start

 

=CALCULATE(SUM(Data[Sales]),DATESBETWEEN(Calendar[Date],MAX(Calendar[Date])+1,DATE(YEAR(MAX(Calendar[Date])),12,31)))

 

There should be a Calendar Table and an active relatiosnhip from the Date column of your source data Table to the Date column of your Calendar Table.  In the Calendar Table, write these calculated column formuals to extract the Month and Year

 

=YEAR(Calendar[Date])

=FORMAT(Calendar[Date],"mmmm")

 

In the Filter/slicer, select a certain Year and Month which you dragged from the Calendar Table.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Hi @Ashish_Mathur ,

How do you modify the formula if you need to add an additional filter based on a selected value?

Below is my formula.  I need to make the 'Data'[Forecast Version] be based on a selectedvalue but don't know how to do this.

 

-This gives me  an error saying I'm using SELECTEDVALUE in a True/False expression

Calculate(SUM('Data'[Value]),
'Data'[Forecast Version] = SELECTEDVALUE([Version]),
DATESBETWEEN('Key: Dates'[Date],MAX('Key: Dates'[Date])+1,DATE(YEAR(MAX('Key: Dates'[Date])),12,31)))/1000000

 

-This doesn't give me an error but returns blank values

Calculate(SUM('Data'[Value]),
FILTER('Data','Data'[Forecast Version] = SELECTEDVALUE([Version])),
DATESBETWEEN('Key: Dates'[Date],MAX('Key: Dates'[Date])+1,DATE(YEAR(MAX('Key: Dates'[Date])),12,31)))/1000000

 

-This one works, but I have a number hard coded in, which I don't want as the user needs to determine the version

Calculate(SUM('Data'[Value]),
'Data'[Forecast Version] = 5,
DATESBETWEEN('Key: Dates'[Date],MAX('Key: Dates'[Date])+1,DATE(YEAR(MAX('Key: Dates'[Date])),12,31)))/1000000
Anonymous
Not applicable

I finally figured it out.  I needed to make make the selection a variable and then return it.  This was about 4 hours spent trying to figure this out.  I really hate Power BI about half the time.

 

The Answer

VAR _version = SELECTEDVALUE([Version])

RETURN

Calculate(SUM('Data'[Value]),
'Data'[Forecast Version] = _version,
DATESBETWEEN('Key: Dates'[Date],MAX('Key: Dates'[Date])+1,DATE(YEAR(MAX('Key: Dates'[Date])),12,31)))/1000000

Hi @Ashish_Mathur,

 

Thanks your solution helped me.

 

Br, Kertu

v-yulgu-msft
Employee
Employee

Hi @Kmur,

 

Based on my assumption, I created below sample table ('Test1') to test.

1.PNG

 

Create a calendar table which is unrelated to 'Test1'. And add a calculated column to get the corresponding date 6 months later. I formatted [Date] to "MMMM yyyy" under Modeling tab. Place [Date] field into slicer.

Dim date =
VAR tempTb =
    CALENDAR ( DATE ( 2018, 1, 1 ), DATE ( 2019, 12, 31 ) )
RETURN
    ADDCOLUMNS (
        FILTER (
            tempTb,
            [Date]
                >= MAXX (
                    FILTER (
                        tempTb,
                        YEAR ( [Date] ) = YEAR ( EARLIER ( [Date] ) )
                            && MONTH ( [Date] ) = MONTH ( EARLIER ( [Date] ) )
                    ),
                    [Date]
                )
        ),
        "Month", MONTH ( [Date] )
    )

6 month later =
DATEADD ( 'Dim date'[Date].[Date], 7MONTH )

Create measures:

start date = SELECTEDVALUE('Dim date'[Date]) +1
End date = SELECTEDVALUE('Dim date'[6 month later])

Total sales = CALCULATE(SUM(Test1[Sales]),DATESBETWEEN(Test1[Date],[start date],[End date]))

2.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Yuliana, 

 

Thank you for you quick reply. Your solution will work as one time calculation but in my case I need the formula working with all months in one year. So that I´d be able to browse through months. Basically logic should be the same as for YTD just to the future. 

 

Br, Kertu

Hi,

 

I am not sure of whom you are replying to.  Have you tried my solution?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.