Summary Total problem

[tvcooper]

I have three tables.  A calendar table, a transaction table and a store table.  I am trying to create a calculation where for any month or range of months that user selects I get only transactions where the store was considered open.  There may be transactions for a store before their open date.  The open date is on the store table - but that table is not associated with the calendar.  The relationship is both calendar and store to transaction with transaction being the many side of both.

I have done several different calculations - all work to pull the total by store (store as rows), but both give erroneous (and different) totals.

This is the full view of one:  ([c-OpenDate] = LASTDATE(Store[Comp Date]).  [ViewBase] is sum of amount field from trans table.

=VAR cMonth = Month(Min(Store[Comp Date]))
VAR cYear = Year(Min(Store[Comp Date]))
VAR calMont = Month(Min('Calendar'[Date]))
VAR calYear = Year(Min('Calendar'[Date]))
VAR isComp = MIN(Store[CompStore])

VAR Result =CALCULATE([ViewBase],FILTER('Store',cYear<=calYear&& cMonth<=calMont&&[c-OpenDate]<>BLANK()))

Return
Result

The other measures are exactly the same (with the calendar variables) except I modify the  "Result".  Here is one variation:

VAR Result =SUMX(FILTER('Store',cYear<=calYear&& cMonth<=calMont&&[c-OpenDate]<>BLANK()), [ViewBase])

I also did this:

VAR Result =CALCULATE(SUMX(Transaction,[ViewBase]),FILTER('Store',cYear<=calYear&& cMonth<=calMont&&[c-OpenDate]<>BLANK()))

Every one works on the row level.  NONE of them produce a correct total.  Any and all help is much appreciated.

[tamerj1]

Hi @tvcooper 

please try

=
SUMX (
FILTER (
Transaction,
FORMAT ( Transaction[Date], "YYYYMM" )
>= FORMAT ( RELATED ( Store[c-OpenDate] ), "YYYYMM" )
&& RELATED ( Store[c-OpenDate] ) <> BLANK ()
),
Transaction[ViewBase]
)

[tvcooper]

Visual is simple:

Store        May2020    June2020         Total

Store 1             500            500           1,000

Store 2                 0            200              200

Total                 500            700           1,200

Store 1 "Opened" May 2020.  Store 2 "Opened" June 2020.  BEWARE...Store 2 HAD sales in May 2020 - but June 2020 is the start date for this calculation.

Slicer for MMM-YYYY

3 tables

Store Table

StoreCode     StartDate      Company

Transaction Table

Date           StoreCode        Amount

Calendar Table

Date             MMMM-YYYY

Joins:

Store - Transaction (StoreCode)

Calendar to Transaction (Date)

[Greg_Deckler]

@tvcooper So the pattern should be:

Measure Total Fixed = 
  VAR __Table = SUMMARIZE('Store', [Store], "__MeasureRowValue", [Your Measure])
  VAR __Result = IF(HASONEVALUE('Store'[Store]), [Your Measure], SUMX(__Table, [__MeasureRowValue]))
RETURN
  __Result

[tvcooper]

Thank you for the reply.  I have read those links and about 1,000 others.  I don't seem to be able to figure it out.  I'd appreciate a specific reply with DAX for my specific problem.  THANK  YOU!

[Greg_Deckler]

@tvcooper Missing a ton of information to be more specific. Starting with how your visual is laid out. What columns are in your visual? Is it a table or a matrix? Sample data would be great. Sample expected output even better. 

Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, 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.

[Greg_Deckler]

@tvcooper First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8