This week in Dax

[Anonymous]

Hi,

I am needing to to do a caulate measure to find out this weeks amount and last weeks amount.

Would it be something like

if(calender.day = Tuesday(calculate(calculate,sum(value),calenderdate-1),calenderdate-1)

What would be the best way to acheive this?

Thanks

Chris

[Greg_Deckler · Accepted solution]

I created the following two enter data queries:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc7BCQAhDEXBXjwvxPysxq1F7L8NQVjwHec2c5Y076bqWdZzlNCAvluqkEOCAnqhBuEiXISLcAlc4roMA/7X2g==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}})
in
    #"Changed Type"

That one is called Calendar.

The next one is called Values:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Rc/BDYAgEAXRXjibwF/QhVoI/behUWe5vtPMnMmzrmxFno6ktI5XHDGkIxUZSPvFCnIiQi7EEEcq0pGGDOSMwgLtaKotqkW2RbbotugW4TXCRXmNcn3pPW/xX+JFT/q6AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Value", Int64.Type}})
in
    #"Changed Type"

Related the tables on "Date" column.

Created the following calculated colunm in both tables:

Weeknum = WEEKNUM([Date])

Created the following Measure:

MyTotal = VAR myMax = MAX('Calendar'[Weeknum]) RETURN CALCULATE(SUM([Value]),ALLEXCEPT('Values','Values'[Weeknum]),'Values'[Weeknum]=myMax)

[Greg_Deckler]

I would think that you would want to use WEEKNUM of a date and compare it with the WEEKNUM of TODAY

WEEKNUM

https://msdn.microsoft.com/en-us/library/ee634572.aspx

TODAY

https://msdn.microsoft.com/en-us/library/ee634554.aspx

[Anonymous]

Nope still does not work

weekly pageviews = CALCULATE(SUM(GA[Pageviews]),WEEKNUM('Calendar$'[Date],2))

Seems to retrun a static number... when i select another month or different week the figure does not change...any ideas?

Im thinking something like the below ...but min does not work

weekly pageviews = CALCULATE(SUM(GA[Pageviews]),DATESBETWEEN('Calendar$'[Date],min(WEEKNUM('Calendar$'[Date],1)),'Calendar$'[Date]))

[danextian]

Do you have a sample data to work with? At least a simple table.

[Anonymous]

Literally there is a date table with the list of dates + the value table with values against dates.

all i am wanting to do is to sum the values for a given week that the user selects from a date slicer.

[Greg_Deckler · Accepted solution]

I created the following two enter data queries:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc7BCQAhDEXBXjwvxPysxq1F7L8NQVjwHec2c5Y076bqWdZzlNCAvluqkEOCAnqhBuEiXISLcAlc4roMA/7X2g==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}})
in
    #"Changed Type"

That one is called Calendar.

The next one is called Values:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Rc/BDYAgEAXRXjibwF/QhVoI/behUWe5vtPMnMmzrmxFno6ktI5XHDGkIxUZSPvFCnIiQi7EEEcq0pGGDOSMwgLtaKotqkW2RbbotugW4TXCRXmNcn3pPW/xX+JFT/q6AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Value", Int64.Type}})
in
    #"Changed Type"

Related the tables on "Date" column.

Created the following calculated colunm in both tables:

Weeknum = WEEKNUM([Date])

Created the following Measure:

MyTotal = VAR myMax = MAX('Calendar'[Weeknum]) RETURN CALCULATE(SUM([Value]),ALLEXCEPT('Values','Values'[Weeknum]),'Values'[Weeknum]=myMax)

[Anonymous]

Hi @Greg_Deckler

Thanks for that works great,  it seems to be suming the full amount for that week though, I have another column on the table which uses lists category against the value.  The category slicer value does not seem to be affecting the category the amount that it is summing, how would I go about using that value.  The date works fine when I select a dat it sums the full amount for that week

Thanks

[Greg_Deckler · Accepted solution]

To make it honor the slicer selection, add that slicer column to the ALLEXCEPT clause.

[Anonymous]

Thanks works perfectly...learning a lot 

[Anonymous]

It doesnt seem to recognise the

join table if I try it doesnt like it and only seems to like measures ...is that correct?

Weekly Targets = VAR myMax = MAX('Calendar$'[Weeknum]) VAR titles = joinTable[website] RETURN CALCULATE(SUM(internetTargets[Impressions]),ALLEXCEPT(internetTargets,internetTargets[weeknum]),internetTargets[weeknum]=myMax)

 If I do

Weekly Targets = VAR myMax = MAX('Calendar$'[Weeknum]) RETURN CALCULATE(SUM(internetTargets[Impressions]),ALLEXCEPT(internetTargets,internetTargets[weeknum]),internetTargets[weeknum]=myMax,joinTable[Website]="News and Star")

It retursn the right amount the question is really how do I go about getting the vlue of the slicer instead of hardcoding the title.

Thanks

[Anonymous]

Hi @Greg_Deckler

It doesnt seem to allow weeknum in the calculate function.

weekly pageviews = CALCULATE(SUM(GA[Pageviews],WEEKNUM()

[Anonymous]

Was missing a bracket!!

Works a treat 🙂